statement “USE @dbname” doesn't work, why? How

问题:

I've got this t-sql snippet:

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 variable (third line of snippet) doesn't work. Why it doesn't work?

回答1:

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

回答2:

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

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

I hope that someone else can do better :-)

回答3:

SQL Server will not accept the USE statement with a variable.

To use database names dynamically, you have 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 using sp_SQLExec

回答4:

The way I do this is with an if statement:

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