asked
Dynamic Query for Multiple Databases
I have created stored procedures that dynamically query specified databases that have the same structure but different data. when I try to use the following stored procedure, I get an error after passing in the database name "Connot find stored procedure SELECT TOP 10...ETC. I just want the query to execute, not find another stored procedure. How should I write the following? I'm using the stored procedure in a reportviewer. ALTER PROCEDURE [dbo].[PROCNAME] @Database varchar(6) AS SET NOCOUNT ON; DECLARE @SQL varchar(400) SET @SQL = 'SELECT DISTINCT TOP (10) Year' + 'FROM' + @Database + 'dbo.Table' + 'ORDER BY' + 'Year DESC' EXECUTE @SQL
So from what I see, you are building the dynamic query, but you do not have spaces between the keywods and also you are missing a dot between database name and the schema. SET @SQL = 'SELECT DISTINCT TOP (10) Year ' + 'FROM ' + @Database + '.dbo.Table ' + 'ORDER BY ' + 'Year DESC' or SET @SQL = N'SELECT DISTINCT TOP (10) Year FROM ' + @Database + N'.dbo.Table ORDER BY Year DESC'
+1 to Pavel, just to add you also need brackets around `@SQL` like this when executing some dynamic SQL: EXECUTE(@SQL) I also just wanted to add, in case it's a possibility, that you could avoid dynamic SQL by instead using a connection to the appropriate database from your calling code and do it that way (yes, you'd need to deploy the sproc to each individual database though)
There is no good guide on this. It's widely accepted that this is generally a database worst practice. It's rather surreal that a database team would mandate this approach. For the query, you MUST include spaces around your string literals. When the database engine translates what you have written, it sees this: SELECTDISTINCTTOP 10 Period_YearFROM databasename.dbo.ViewName ORDER BYPeriod_YearDESC Just like us, the database engine needs spaces between the words. Replace `EXECUTE(@SQL)` with `PRINT @SQL`. You can then adjust the concatenated string until you have something that executes, at which point you put the `EXECUTE(@SQL)` back in place.
As @ThomasRushton if you have space, hyphen or other special character in the name, you need to put that name into the brackets. If you have that name in a variable eg. in case of the database. you can use the **`QUOTENAME`** function: `QUOTENAME(@database)`. This will automatically add a brackets around the object name. You can also try to use: DECLARE @SQL nvarchar(400) SET @SQL = N'USE ' + QUOTENAME(@Database) + N'; SELECT DISTINCT TOP 10 Period_Year FROM dbo.ViewName ORDER BY Period_year DESC' EXEC (@sql) But definitely as several times mentioned here, if you are constructing a dynamic sql and concatenating string, you have to ensure, that in the final string there will be spaces before and after keywords etc.
Did you say you had a hyphen in your table name? if so, you will need to wrap the table name in square brackets. eg: SELECT * FROM [dbname].[schemaowner].[tablename]
replace the left parenthesis in your where clause with a . add a left parenthesis after the WHERE... ...Num ' + 'WHERE (' + (@Database) + '.dbo.ViewNameJP.Mas... also, because you added the USE statement (and this makes things simpler), you no longer need to insert @Database into the various places in the query. Here is a WHERE clause without it... ...Num ' + 'WHERE (dbo.ViewNameJP.Mas...
@technette - sometimes just throw in a PRINT statement and see what @sql is... you'll see the lack of spaces pretty quick ;)
