SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 USE DATABASE - dynamic variable?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Rasta Pickles
Posting Yak Master

United Kingdom
169 Posts

Posted - 05/14/2013 :  07:56:25  Show Profile  Reply with Quote
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....


The USE command doesn't work in this context.

Any advice appreciated.

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 05/14/2013 :  08:00:14  Show Profile  Reply with Quote
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
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1050 Posts

Posted - 05/14/2013 :  08:01:44  Show Profile  Reply with Quote
Check by replacing
"THEN @dbinit + 'North'"
with
"THEN 'Use '+@dbinit + 'North'
in each case of the CASE statement

Cheers
MIK
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
169 Posts

Posted - 05/14/2013 :  08:25:02  Show Profile  Reply with Quote
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


Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1600 Posts

Posted - 05/14/2013 :  11:57:14  Show Profile  Reply with Quote
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)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 05/15/2013 :  00:31:16  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000