sql use statement with variable

Question

I'm trying to switch the current database with a SQL statement. I have tried the following, but all attempts failed:

-- 1
USE @DatabaseName

-- 2
EXEC sp_sqlexec @Sql -- where @Sql = 'USE [' + @DatabaseName + ']'

To add a little more detail.

EDIT: I would like to perform several things on two separate database, where both are configured with a variable. Something like this:

USE Database1
SELECT * FROM Table1

USE Database2
SELECT * FROM Table2

Kinakinabalu

Following Preet's information, you're going to have to put all the queries for the USE/SELECT into a single sp_sqlexec call. The USE will not survive beyond the call. – Peng

I know of this solution, but it is ugly and unreadable. Having a script generating the final SQL would also be an option. – Kinakinabalu

Sorry I thought you were just looking for a solution within SQL itself. Within SQL, you've only got the sp_sqlexec approach; the other option is to construct the SQL sequence of USE/SELECT externally. You'll still need to construct the SQL by hand but, on the plus side, your chosen scripting/application environment will be easier on string manipulation and not look so ugly. – Peng

Answer 1

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

Peng

Preet is correct with the above. Although the USE will work for the @sql statement, it won't be a permanent change. Having a paramterised USE would also present all sorts of performance/compilation implications, and I can't see any alternative working. – goodgai 0 secs ago [delete this comment] – Peng

Although successful, This doesn't convert the database – Avifauna

Answer 2

   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/

Athwart

As said in the question this does not work. For instance if I run a SELECT command afterwards like: SELECT * FROM Table, an Invalid object name error is thrown – Kinakinabalu

the select must be in the @SQL - as I said the use is only for the duration on the @sql – Athwart

It is kind of a solution, but useless for my purpose. – Kinakinabalu

Answer 3

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.

Kibitzer

I know I'm answering questions from 2009, but hopefully someone like me will find here something that they can use. – Kibitzer

This solution is awesome as it ACTUALLY WORKS!!! I can just put all my schema names in a stored procedure with this and then call the appropriate USE statement if there's a match. Brilliant. (It even validates they're in an approved set of names) – Sharyl

Just spotted this!. Good answer! – Athwart

Unfortunately this doesn't appear to work when the databases exist on different servers. (PROD_SVR.PROD_DB, UAT_SVR.UAT_DB, IT_SVR.IT_DB, etc.) It appears SSMS validates the existence of the database for all USE statements in the script, even if those lines aren't executed. I was trying a variant of: IF @@SERVERNAME = 'UAT_Server' BEGIN USE UAT_Database GOTO Script END I think only dynamic SQL works in this case. – Aldershot

@Skerkles Good catch! If you have the specific script feel free to send an edit to my answer. Otherwise, just let me know how you did it and I will update the answer – Kibitzer

While it works in some cases, it still does not a provide a way of "dynamic" database change. I.e. if you have an active database name stored in a database table and you want to apply particular script on the active database. It's not a criticism, just users should be aware of the limits. – Exsiccate

Answer 4

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)

Kashakashden

Answer 5

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)

Whipcord

I'm aware of this solution, but it is usless for my purpose. – Kinakinabalu

Answer 6

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.

Student

And how do I access the content of the table? – Streamlet

Answer 7

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

Acrobatic

Answer 8

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)

Tamer

Woops, just noticed this is practically the same as was already said! – Tamer

Answer 9

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

Size

Answer 10

-- 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

Relict