April 25, 2003 at 1:49 pm
Anyone know a way to use the "USE <databasename>" command dynamically?
I need to insert code into a T-SQL script to change the database, create a stored procedure, then switch back to the first database before the rest of the script executes. I've gotten everything to work except switching back to the first database.
I have the name of the first database in a variable, but it raises an error when I try:
USE @db
I've tried assigning it to a string and executing it, but while it gives no error, it doesn't change the db:
SET @str = 'USE ' + @db
EXEC (@str)
Any assistance would be greatly appreciated.
JD
April 25, 2003 at 3:25 pm
When you issue the "EXEC" statement it executes the USE statement outside the scope of your current session. Basically it starts a new session, executes the USE statement, then returns to your calling session.
If your code for your SP is not to large you could do the following:
declare @CMD char(1000)
set @CMD = 'use test ' + char(13) + 'declare @cmd char(1000)' + char(13) +
'set @cmd = ''create proc dbo.test as print ''''x'''' ''' + char (13) +
'exec (@cmd)'
print @cmd
exec (@cmd)
exec test.dbo.test
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
April 28, 2003 at 8:02 am
Beautiful! Worked perfectly -- now my outide script never changes context and my problem is solved. I owe you one, Greg!
JD
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply