SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The USE command and variables


The USE command and variables

Author
Message
Andrew Ryan
Andrew Ryan
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 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.
terrance.steadman
terrance.steadman
SSC Veteran
SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)

Group: General Forum Members
Points: 299 Visits: 538
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 =
SET @SQLString = 'USE ' + @DBIn + '; SELECT * FROM MyTable'

EXEC(@SQLString)




Have a good day.

Terry Steadman
Florian Reischl
Florian Reischl
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3673 Visits: 3934
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
Sgar...
Sgar...
Old Hand
Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)

Group: General Forum Members
Points: 359 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!!Cool
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11258 Visits: 12005
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89265 Visits: 41143
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Sgar...
Sgar...
Old Hand
Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)

Group: General Forum Members
Points: 359 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!!Cool
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11258 Visits: 12005
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89265 Visits: 41143
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Sgar...
Sgar...
Old Hand
Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)Old Hand (359 reputation)

Group: General Forum Members
Points: 359 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!!Cool
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search