USE DATABASE - dynamic variable? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

USE DATABASE - dynamic variable?

Author

Topic

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2013-05-14 : 07:56:25

Last resort, I've googled this and it doesn't appear possible but maybe some of the geniuses here know a workaround.

Here's my code, it is hopefully clear what I'm trying to do:

[CODE]
DECLARE @counter INT
DECLARE @dbname SYSNAME
DECLARE @dbinit VARCHAR (12)
DECLARE @worktable AS TABLE (DB_SOURCE VARCHAR(50)
,PERIOD VARCHAR(6)
,PL_FIGURE DECIMAL(10, 2))

SET @dbinit = 'Parcels_'
SET @counter = 1

WHILE @counter < 6
BEGIN

SET @dbname = CASE @counter
WHEN 1 THEN @dbinit + 'North'
WHEN 2 THEN @dbinit + 'South'
WHEN 3 THEN @dbinit + 'East'
WHEN 4 THEN @dbinit + 'West'
ELSE @dbinit + 'Unspecified'
END

EXEC ('USE [' + @dbname +']')
rest of SQL script follows....
[/code]

The USE command doesn't work in this context.

Any advice appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-14 : 08:00:14

you cant do it like this
Can I ask why you need to change db context at runtime?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-05-14 : 08:01:44

Check by replacing
"THEN @dbinit + 'North'"
with
"THEN 'Use '+@dbinit + 'North'
in each case of the CASE statement

Cheers
MIK

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2013-05-14 : 08:25:02

Mainly for convenience, there are twelve databases and the script is 400 lines long.

quote:
Originally posted by visakh16

you cant do it like this
Can I ask why you need to change db context at runtime?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-05-14 : 11:57:14

The USE database actually does work BUT it only works within the context of the actual dynamic SQL statement. Once the EXEC returns, the original context (i.e. your current connection) becomes the context that your script runs in. You could make your script a part of your dynamic SQL with the USE Database the first line. This can get messy quickly. We handle this conundrum by making the connections at the application level and executing the script against each connection.

=================================================
I am not one of those who in expressing opinions confine themselves to facts. (Mark Twain)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-15 : 00:31:16

quote:
Originally posted by Rasta Pickles

Mainly for convenience, there are twelve databases and the script is 400 lines long.

quote:
Originally posted by visakh16

you cant do it like this
Can I ask why you need to change db context at runtime?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

One alternative you can use is system stored procedure sp_msforeachdb which will iteratethrough all databases in server. You could add check inside that to make sure its one of those 12 dbs and then write your code inside that condition

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources