USE DATABASE
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
Software/Hardware used:
Software/Hardware used:
ASKED:
November 7, 2007 1:40 PM
UPDATED:
December 2, 2007 12:09 PM
Answer Wiki:
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.
<pre>declare @db varchar(10)
set @db = 'db1'
declare @cmd varchar(8000)
set @cmd = 'select * from ' + @db
exec (@cmd)</pre>
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
