question

technette avatar image
technette 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
executecross-database
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image
Pavel Pawlowski answered
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'
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

AdaTheDev avatar image
AdaTheDev answered
+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)
12 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
3 Likes 3 ·
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.
2 Likes 2 ·
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]
1 Like 1 ·
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...
1 Like 1 ·
@technette - sometimes just throw in a PRINT statement and see what @sql is... you'll see the lack of spaces pretty quick ;)
1 Like 1 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.