Unable to pass variable to USE keyword

Hi,

I am not sure why we cannot pass variables to USE keyword, e.g. USE @dbname.  I have tried using EXEC sp_executesql @dbexec, where @dbname contains the value 'USE ' + @dbname, however, it will not really switch to the new database.

E.g.
declare @dbname nvarchar(30)
declare @dbexec nvarchar(30)

set @dbname = 'new_test_db'
set @dbexec = 'USE ' + @dbname

exec sp_executesql @dbexec

Now that it is executed, it will of course change back to Master (this also the case if we use EXEC (@dbname).  I would like it to remain in the new database that I have chosen, e.g. new_test_db so that I can manipulate more data inside there as per the USE keyword.

How can I really accomplish this in SQL Server 2000 Query Analyzer please.  I would like to do this as we have lots of databases that ends with a numbers, so that I can perform a loop.

I thank you in advance for your help.
Charmed_EmpressAsked:
Who is Participating?
 
Eugene ZConnect With a Mentor Commented:
you can use it if you put the t-sql in the same variable:
for example:

declare @dbname nvarchar(30)
declare @dbexec nvarchar(30)

set @dbname = 'new_test_db'
set @dbexec = 'USE ' + @dbname + ' Select * from sometable'

exec (@dbexec)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you cannot do it, sorry to say.
0
 
nmcdermaidCommented:
Don't know if it helps but you can do this:

EXEC <dbname>.<schemaname>.<stored proc name>


i.e.

EXEC YourDB.dbo.p_AStoredProcedure



or this


SELECT * FROM YourDB.dbo.YourTable


In other words you can prefix the object with a database name.



.... and I beleive you can use this format in sp_executsql.




declare @dbname nvarchar(30)
declare @dbexec nvarchar(30)

set @dbname = 'TestDB'
set @dbexec = 'SELECT * FROM ' + @dbname + '.dbo.YourTable'
sp_executesql @dbexec
0
 
Gautham JanardhanCommented:
dont really know what you are trying to do but if your purpose is to select  or do some transaction
onanother database other that the currenct one then you could always use something like this


select * from [northwind].dbo.custiomers

or insert into [northwind].dbo.custiomers
values ('','','')
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
My Suggestion:
  accept EugeneZ's comment
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.