Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 992
  • Last Modified:

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.
0
Charmed_Empress
Asked:
Charmed_Empress
1 Solution
 
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
 
Eugene ZCommented:
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
 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now