Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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, June 28, 2016 10:14 AM
Points: 181, Visits: 533
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: Thursday, February 5, 2015 1:08 AM
Points: 1,893, Visits: 3,934
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
Posted Friday, April 15, 2016 5:53 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, June 6, 2016 1:36 AM
Points: 179, Visits: 666
In above examples you have used Set @dbname= 'database name'.

I am taking this input from Stored procedure input. How can you help?


Sagar Sonawane
** Every DBA has his day!!
Post #1778129
Posted Friday, April 15, 2016 10:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:47 PM
Points: 7,652, Visits: 10,689
Sgar... (4/15/2016)
In above examples you have used Set @dbname= 'database name'.

I am taking this input from Stored procedure input. How can you help?


In that case you do not need the SET, because the parameter will be set to the value you pass in the EXECUTE statement.
Please read very carefully all that has been said in this topic. And then read up on SQL injection. Do all that before coding any further line of code that takes you on the path of using a variable or parameter to determine the database to use.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1778260
Posted Friday, April 15, 2016 8:11 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 40,971, Visits: 38,266
Sgar... (4/15/2016)
In above examples you have used Set @dbname= 'database name'.

I am taking this input from Stored procedure input. How can you help?


I'm curious... have you ever written a stored procedure before this?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

Helpful Links:
How to post code problems
How to post performance problems
Post #1778420
Posted Saturday, April 16, 2016 1:26 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, June 6, 2016 1:36 AM
Points: 179, Visits: 666
Andrew Ryan (7/9/2009)
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.


i was talking about hardcoding database name in procedure. In my procedure @dbname will be input parameter, it cannot be hardcoded. Hope its clear now.


Sagar Sonawane
** Every DBA has his day!!
Post #1778432
Posted Saturday, April 16, 2016 1:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:47 PM
Points: 7,652, Visits: 10,689
Sgar... (4/16/2016)
Andrew Ryan (7/9/2009)
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.


i was talking about hardcoding database name in procedure. In my procedure @dbname will be input parameter, it cannot be hardcoded. Hope its clear now.


Yes, that is how I interpreted your question. Did you read my reply?



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1778435
Posted Saturday, April 16, 2016 9:02 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 40,971, Visits: 38,266
Jeff Moden (4/15/2016)
Sgar... (4/15/2016)
In above examples you have used Set @dbname= 'database name'.

I am taking this input from Stored procedure input. How can you help?


I'm curious... have you ever written a stored procedure before this?


To be sure, I'm not asking this question to be snarky. According to what you've posted, it appears that you've never written a parameterized stored procedure and we need to know what level you're at to make a better suggestion.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

Helpful Links:
How to post code problems
How to post performance problems
Post #1778450
Posted Wednesday, April 20, 2016 3:32 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, June 6, 2016 1:36 AM
Points: 179, Visits: 666
I had written couple of SP's but such requirement never occurred.

Thank you Hugo and Jeff.


Sagar Sonawane
** Every DBA has his day!!
Post #1779303
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse