sql use statement with variable

The problem with the former is that what you're doing is USE 'myDB' rather than USE myDB. you're passing a string; but USE is looking for an explicit reference.

The latter example works for me.

DECLARE @sql varchar(20)
SELECT @sql = 'USE myDb'
EXEC sp_sqlexec @Sql

-- Also works
SELECT @sql = 'USE [myDb]'
EXEC sp_sqlexec @Sql

   exec sp_execsql @Sql

The DB change only lasts for the time to complete @sql

http://blog.sqlauthority.com/2007/07/02/sql-server-2005-comparison-sp_executesql-vs-executeexec/

I have the same problem, I overcame it with an ugly -- but useful -- set of GOTOs.

The reason I call the "script runner" before everything is that I want to hide the complexity and ugly approach from any developer that just wants to work with the actual script. At the same time, I can make sure that the script is run in the two (extensible to three and more) databases in the exact same way.

GOTO ScriptRunner

ScriptExecutes:

--------------------ACTUAL SCRIPT--------------------
-------- Will be executed in DB1 and in DB2 ---------
--TODO: Your script right here

------------------ACTUAL SCRIPT ENDS-----------------

GOTO ScriptReturns

ScriptRunner:
    USE DB1
    GOTO ScriptExecutes

ScriptReturns:
    IF (db_name() = 'DB1')
    BEGIN
        USE DB2
        GOTO ScriptExecutes
    END

With this approach you get to keep your variables and SQL Server does not freak out if you happen to go over a DECLARE statement twice.

Just wanted to thank KM for his valuable solution. I implemented it myself to reduce the amount of lines in a shrinkdatabase request on SQLServer. Here is my SQL request if it can help anyone :

-- Declare the variable to be used
DECLARE @Query varchar (1000)
DECLARE @MyDBN varchar(11);
-- Initializing the @MyDBN variable (possible values : db1, db2, db3, ...)
SET @MyDBN = 'db1';
-- Creating the request to execute
SET @Query='use '+ @MyDBN +'; ALTER DATABASE '+ @MyDBN +' SET RECOVERY SIMPLE WITH NO_WAIT; DBCC SHRINKDATABASE ('+ @MyDBN +', 1, TRUNCATEONLY); ALTER DATABASE '+ @MyDBN +' SET RECOVERY FULL WITH NO_WAIT'
-- 
EXEC (@Query)

try this:

DECLARE @Query         varchar(1000)
DECLARE @DatabaseName  varchar(500)

SET @DatabaseName='xyz'
SET @Query='SELECT * FROM Server.'+@DatabaseName+'.Owner.Table1'
EXEC (@Query)

SET @DatabaseName='abc'
SET @Query='SELECT * FROM Server.'+@DatabaseName+'.Owner.Table2'
EXEC (@Query)

I case that someone need a solution for this, this is one:

if you use a dynamic USE statement all your query need to be dynamic, because it need to be everything in the same context.

You can try with SYNONYM, is basically an ALIAS to a specific Table, this SYNONYM is inserted into the sys.synonyms table so you have access to it from any context

Look this static statement:

CREATE SYNONYM MASTER_SCHEMACOLUMNS FOR Master.INFORMATION_SCHEMA.COLUMNS
SELECT * FROM MASTER_SCHEMACOLUMNS

Now dynamic:

DECLARE @SQL VARCHAR(200)
DECLARE @CATALOG VARCHAR(200) = 'Master'

IF EXISTS(SELECT * FROM  sys.synonyms s WHERE s.name = 'CURRENT_SCHEMACOLUMNS')
BEGIN
DROP SYNONYM CURRENT_SCHEMACOLUMNS
END

SELECT @SQL = 'CREATE SYNONYM CURRENT_SCHEMACOLUMNS FOR '+ @CATALOG +'.INFORMATION_SCHEMA.COLUMNS';
EXEC sp_sqlexec @SQL

--Your not dynamic Code
SELECT * FROM CURRENT_SCHEMACOLUMNS

Now just change the value of @CATALOG and you will be able to list the same table but from different catalog.

If SQLCMD is an option, it supports scripting variables above and beyond what straight T-SQL can do. For example: http://msdn.microsoft.com/en-us/library/ms188714.aspx

You can do this:

Declare @dbName nvarchar(max);
SET @dbName = 'TESTDB';

Declare @SQL nvarchar(max);
select @SQL = 'USE ' + @dbName +'; {can put command(s) here}';
EXEC (@SQL);

{but not here!}

This means you can do a recursive select like the following:

Declare @dbName nvarchar(max);
SET @dbName = 'TESTDB';
Declare @SQL nvarchar(max);

SELECT @SQL = 'USE ' + @dbName + '; ' +(Select ... {query here}
For XML Path(''),Type)
.value('text()[1]','nvarchar(max)');

Exec (@SQL)

November 2025, 16 years and 5 months after the question was asked ... it's never too late!!!

I have a script with 200+ commands and want to schedule the script to run overnight via sqlcmd against any database I specify. Passing databasename to the script is straightforward but how to then ensure that USE databasename will hold for every command.

The approach I came up with is to create a temporary procedure at the beginning of the script that I can then repeatedly call passing @databasename and @sql. Note that the temporary procedure is automatically dropped when the session ends. The script below demonstrates the approach.

-- drop procedure - useful for testing
-- IF OBJECT_ID('tempdb..#sp_querydb') IS NOT NULL DROP PROCEDURE #sp_querydb

/* SAMPLE SCRIPT */
-- create temp proc
CREATE PROCEDURE #sp_querydb @db nvarchar(100), @query varchar(500)
AS
  BEGIN
    SET NOCOUNT ON
    DECLARE @sql nvarchar(1000)
    SELECT @sql = 'USE ' + @db + '; ' + @query + ';'
    EXEC sp_executesql @sql  -- or sp_sqlexec
  END
GO

-- execute commands
EXEC #sp_querydb 'mydb', 'SELECT DB_NAME()'
EXEC #sp_querydb 'myotherdb', 'SELECT DB_NAME()'
GO

/* OUTPUT */
-------------
mydb
-------------
myotherdb

Use exec sp_execsql @Sql

Example

DECLARE @sql as nvarchar(100)  
DECLARE @paraDOB datetime  
SET @paraDOB = '1/1/1981'  
SET @sql=N'SELECT * FROM EmpMast WHERE DOB >= @paraDOB'  
exec sp_executesql @sql,N'@paraDOB datetime',@paraDOB

-- If you are using a variable for the database name. 
-- Try something like this. 

DECLARE @DBName varchar(50)
Set @DBName = 'Database1'; /*  could be passed in by a parameter. */

IF( @DBName = 'Database1')
Begin
    USE [Database1];
SELECT  FROM Table1;
End

IF( @DBName = 'Database2')
Begin
USE [Database2];
SELECT  FROM Table2;
End

IF( @DBName is null)
Begin
USE [Database1];
End