The USE command and variables

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » The USE command and variables

The USE command and variables

Rate Topic

Display Mode

Topic Options

Author

Message

Andrew Ryan

Posted Thursday, July 09, 2009 1:32 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, February 21, 2012 11:58 AM
Points: 2, Visits: 31

Sorry if this has been covered but as you might imagine searching for this has been problematic.

I want to use vaariables with my USE commands.

USE @DBName
GO

if I use that I get an error that points to an arbitrary line in the code.

if I use this the database does not change but executes with no errors.

DECLARE @DBName char(3), @X as varchar(MAX)
SET @DBName = 'SBU'
set @X = 'use ' + @DBName +';'
EXEC(@X);

I want to dynamically change which DB I am using. I could just be going about this the wrong way.

"From the smallest necessity to the highest religious abstraction, from the wheel to the skyscraper, everything we are and everything we have comes from one attribute of man - the function of his reasoning mind.

Post #750586

terrance.steadman

Posted Thursday, July 09, 2009 1:39 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 19, 2013 11:06 AM
Points: 174, Visits: 423

Greetings Andrew,

I may be incorrect about this, but I suspect that the EXEC() operates like a batch. So, it is changing your DB focus, but when it finishes, so does your focus and it switches to the last active DB. You might need to wrap your extra code within the EXEC().


DECLARE @SQLString  varchar(max)
DECLARE @DBIn  varchar(50)

SET @DBIn = <The Database You Want>
SET @SQLString = 'USE ' + @DBIn + '; SELECT * FROM MyTable'

EXEC(@SQLString)

Have a good day.

Terry Steadman

Post #750594

Florian Reischl

Posted Thursday, July 09, 2009 2:01 PM

SSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932

Andrew Ryan (7/9/2009)

I want to use vaariables with my USE commands.

USE @DBName

You cannot specify the database name as variable for a USE call.

if I use this the database does not change but executes with no errors.

DECLARE @DBName char(3), @X as varchar(MAX)
SET @DBName = 'SBU'
set @X = 'use ' + @DBName +';'
EXEC(@X);

This works, but consider the execution context. The USE statement will be executed as own statement within your EXEC without affecting your outer context.

You can either use the syntax Terrance provided and add your statement into the variable, or enable SQLCMD mode (Menu -> Query -> SQLCMD Mode) within your script like this:

:setvar DatabaseName "Sandbox"

USE [$(DatabaseName)]

GO

SELECT COUNT(*) FROM Tally

The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems

Post #750610

« Prev Topic | Next Topic »

Permissions