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.

KenJ avatar image KenJ commented ·
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 ·
Pavel Pawlowski avatar image Pavel Pawlowski commented ·
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 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
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 ·
KenJ avatar image KenJ commented ·
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 ·
Blackhawk-17 avatar image Blackhawk-17 commented ·
@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 ·
Pavel Pawlowski avatar image Pavel Pawlowski commented ·
+1 Yeah.. I missed the brackets.. :-)
0 Likes 0 ·
technette avatar image technette commented ·
@AdaTheDev, I originally had each report pointing to its own database but the database team wanted me to change to this dynamic querying. My reports were working beautifully and are ready for release to production :( I'm still getting errors. Does this work differently with views? After trying the following, I get errors that the 'Year' column does not exist. DECLARE @SQL varchar(400) SET @SQL = 'SELECT' + '(10) Year' + 'FROM' + @Database + 'VIEW ORDER BY Year DESC' EXECUTE (@SQL)
0 Likes 0 ·
Oleg avatar image Oleg commented ·
@technette You are missing - distinct top to the left of 10 - space before and after FROM - dot before VIEW (should be something like .dbo.ViewName) in your snippet.
0 Likes 0 ·
technette avatar image technette commented ·
Is there a good guide on this. I have 10 stored procedures that are more complicated to debug and I'm still getting an error on one. In this example. I have included a hyphen for a column 'Period_Year'. I'm getting a syntax error near 'Period_Year'. DECLARE @SQL varchar(400) SET @SQL = 'SELECT' + 'DISTINCT' + 'TOP 10 Period_Year' + 'FROM ' + @Database + '.dbo.ViewName ORDER BY' + 'Period_Year' + 'DESC' EXECUTE (@SQL)
0 Likes 0 ·
technette avatar image technette commented ·
Thank you Pavel, Thomas, Ken, Oleg! I am making progress. I made it to sp number 4... I really appreciate your help. For this particular one, I'm getting an incorrect syntax near '=' don't know why... DECLARE @SQL varchar(400) SET @SQL = N'USE ' + QUOTENAME(@Database) + N'; SELECT DISTINCT ViewNameJP.PR_Num FROM ViewNameJP INNER JOIN ViewNameJM ON ViewNameJP.PR_Num = ViewNameJM.PR_Num ' + 'WHERE ' + (@Database) + '(dbo.ViewNameJP.Master_Number =' + Convert (Varchar (11), @Master_Number) + ')' + 'ORDER BY ViewNameJP.PR_Num' EXECUTE (@SQL)
0 Likes 0 ·
technette avatar image technette commented ·
Thanks for responding Blackhawk! Where the PRINT print to? When I put PRINT @SQL It just executes the altered stored procedure.
0 Likes 0 ·
Pavel Pawlowski avatar image Pavel Pawlowski commented ·
Hi instead of calling `EXECUTE (@sql)` you just call `PRINT @sql`. When you call the proc from within SSMS, you will see a competly constructed SQL Statement in the Messages Tab of the output part of window.
0 Likes 0 ·

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.