Question

USE DATABASE

USE DATABASE, Database, SQL, Stored procedures

How can I write a stored procedure which can switch to new databases using something like "use DBASENAME", but without knowing the dbase names before the procedure runs? The example below was my attempt to do this using SP_EXECUTESQL. The reason for this is so that I can call stored procedures within my stored procedure so that they will use the data in the desired dbase.


set nocount on
use demo

declare @NSqlStatment as nvarchar(500)
declare @DbaseName as varchar(20)

print db_name()

set @DbaseName = 'poports'
SET @NSqlStatment = 'use ' + @DbaseName
EXEC SP_EXECUTESQL @NSqlStatment
print db_name()

use poports
print db_name()


RESULTS:

demo
demo
poports

In order to do this everything which needs to be done within the other database needs to be done in the dynamic SQL. Dynamic SQL is done out of bounds of the parent code. Using this method it would be much better to simply access the objects without the USE command.

declare @db varchar(10)
 set @db = 'db1'
 declare @cmd varchar(8000)
 set @cmd = 'select * from ' + @db
 exec (@cmd)