July 9, 2009 at 1:32 pm
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.
July 9, 2009 at 1:39 pm
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
July 9, 2009 at 2:01 pm
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
April 15, 2016 at 5:53 am
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:
April 15, 2016 at 10:20 am
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.
April 15, 2016 at 8:11 pm
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
Change is inevitable... Change for the better is not.
April 16, 2016 at 1:26 am
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:
April 16, 2016 at 1:52 am
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?
April 16, 2016 at 9:02 am
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
Change is inevitable... Change for the better is not.
April 20, 2016 at 3:32 am
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:
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply