The statement "USE @dbname" does not work, why? How to do it?

Question

I have this t-sql fragment:

DECLARE @db_name varchar(255); SET @db_name = 'MY_DATABASE'; -- assuming there is database called 'my_database' USE @db_name -- this line ends with error "Incorrect syntax near '@db'." 

But USE with a variable (the third line of the fragment) does not work. Why is this not working?

Answer 1

You cannot specify the database name for the USE statement in a variable.

Answer 2

As you noticed, the USE statement does not accept a variable as a parameter. The only alternative that quickly comes to mind is rather rude and extremely error prone, but here you go:

 EXEC ('USE ' + @db_name + ' SELECT * FROM some_table INSERT INTO some_table VALUES (1)') 

I hope someone else can do better :-)

Answer 3

SQL Server does not accept a USE statement with a variable.

To use database names dynamically, you need to create dynamic SQL statements with (almost) fully qualified names as follows:

 Declare @SQL VarChar (100) SET @SQL = 'SELECT * FROM ' + @DatabaseName + '.dbo.TableName' 

and then you execute it with sp_SQLExec

Answer 4

How do I do this with an if statement:

 if @DBName = 'DB1' <query with DB1> else <query with DB2>