Can I dynamically select a database in a stored procedure?

[tonydismukes]

I'm trying to clean up some highly inefficient ColdFusion code which loops through a long list of databases, running dynamically built SQL queries on each one. I'm considering moving the entire process into a stored procedure and would like to do something like the following:

/* simplified, but gives basic idea */

use AdminDB

declare @theDB varchar(40)
declare @theTable varchar(40)
declare @fooCount int
declare @fooTotalCount int
declare dbCursor CURSOR for
	Select dbName, tableName from TableList

select @fooTotalCount = 0
open dbCursor
fetch next from dbCursor into @theDB, @theTable
while @@FETCH_STATUS = 0
begin
     use @theDB
     select @fooCount = count(*) from @theTable where status = 'foo'
     set @fooTotalCount = fooTotalCount + @fooCount
     fetch next from dbCursor into @theDB, @theTable
end
close dbCursor
deallocate dbCursor

/* do stuff with fooTotalCount down here */

Unfortunately, this doesn't work, since SQL Server doesn't like the line 'use @theDB'. Is there a syntax which will work?

I know there will be votes for not using the dynamic SQL at all, but rather writing a separate stored procedure for each DB, so that the procedures can be optimized. I'm sort of in favor of that myself. The problem is that we're a small shop currently without a designated DBA, and we've got a number of developers creating databases at once. I might be transitioning to more of a DBA position over the next year, but I'm not sure I currently have the leverage to hold up each new database until I write the needed stored procedures for each one.

So currently my options are
1) move the looping into TSQL, as shown above (if possible)
2) create stored procedures for each DB and call them dynamically from a loop (may not be able to negotiate this sort of procedure at present time)
3) leave the looping in the ColdFusion code, but clean up the CF code to be more efficient (however, currently our ColdFusion server is much more overloaded than our SQL Server, so I wouldn't mind dumping some of the load off of CF and onto SQL)

Any advice is welcome. Thanks in advance.

 

[JayKusch]

Lets try this as your Begin/End code ...

use AdminDB

DECLARE @SQL_Command VarChar(4000)

declare @theDB varchar(40)
declare @theTable varchar(40)
declare @fooCount int
declare @fooTotalCount int
declare dbCursor CURSOR for
Select dbName, tableName from TableList

select @fooTotalCount = 0
open dbCursor
fetch next from dbCursor into @theDB, @theTable
while @@FETCH_STATUS = 0
begin

SET @SQL_Command = 'select @fooCount = count(*) from ' + RTRIM(LTRIM(@theDB)) + '.dbo.' + RTRIM(LTRIM(@theTable)) + ' where status = '''' + 'foo' + '''' '
set @fooTotalCount = fooTotalCount + @fooCount
fetch next from dbCursor into @theDB, @theTable
end
close dbCursor
deallocate dbCursor


Thanks

J. Kusch

 

[SteveBrett]

have a look at the undocumented stored procedure ms_ForEachDB
at this link - it might help you

http://www.devx.com/getHelpOn/10MinuteSolution/20551

 

[tonydismukes]

Steve - thanks, but in the actual application, I have to select a particular list of databases based on customer ID and a particular table for each DB. A stored procedure which goes through every database on the server doesn't help me.

Jay - the code you present builds the appropriate string for the query and places it in the @SQL_Command variable, but it doesn't actually run the query. Now how do I actually run the query?

 

[JayKusch]

DOH ... forgot the most important part ... SORRY!!!

SET @SQL_Command = 'select @fooCount = count(*) from ' + RTRIM(LTRIM(@theDB)) + '.dbo.' + RTRIM(LTRIM(@theTable)) + ' where status = '''' + 'foo' + '''' '

     [b]EXEC (@SQL_Command)[/b]

     set @fooTotalCount = fooTotalCount + @fooCount


     fetch next from dbCursor into @theDB, @theTable

Thanks

J. Kusch

 

[tonydismukes]

Hmmm, that's getting closer. Now, however, when the script reaches the EXEC (@SQL_Command) line, it gives me the following message:

Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@fooCount'.

@fooCount is previously declared in my script, and I can access it before and after the EXEC(@SQL_Command) line. I'm thinking that the EXEC command must be treating @SQL_Command as a separate stored procedure, without access to the variables previously declared in the current script.

...Checks reference book ...

Yep, apparently variables inside of and outside of an EXEC statement have different scopes. The only way to communicate between them is through the use of temporary tables. (Whose bright idea was that?) I'll try it out with the temp tables, but I'll have to do some testing to see whether I actually gain any net benefit over the ColdFusion looping by the time I'm done.

Thanks for your help, Jay.

Anyone else have any suggestions?

 

[JayKusch]

begin

SET @SQL_Command = 'select ' + @fooCount + ' = count(*) from ' + RTRIM(LTRIM(@theDB)) + '.dbo.' + RTRIM(LTRIM(@theTable)) + ' where status = '''' + 'foo' + '''' '
set @fooTotalCount = fooTotalCount + @fooCount
fetch next from dbCursor into @theDB, @theTable
end


Thanks

J. Kusch