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)