Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

The USE command and variables Expand / Collapse
Author
Message
Posted Thursday, July 9, 2009 1:32 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, July 9, 2009 1:39 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 7:21 AM
Points: 176, Visits: 461
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
Posted Thursday, July 9, 2009 2:01 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, 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 »

Add to briefcase

Permissions Expand / Collapse