[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Simple Dynamic SQL Statement

Posted on 2009-04-17
2
Medium Priority
?
329 Views
Last Modified: 2012-05-06
Whenever I run the following stagement the message window tells me that the statement completed successfully but it never changes my database.  I am researching the ability to programatically change the database I'm working with without having to fully qualify database objects throughout my code.
/***************************************************/
DECLARE @STAGE VARCHAR(50) = 'IMANuclear_Stage_Tmp'
DECLARE @ODS VARCHAR(50) = 'IMANuclear_ODS_Tmp'
DECLARE @SQLCMD VARCHAR(300)
/***************************************************/
 
SET @SQLCMD = 'USE ' + @STAGE
EXEC (@SQLCMD)
GO

Open in new window

0
Comment
Question by:ltrain2015
2 Comments
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 200 total points
ID: 24167402
that is because it changes the scope of the db inside of the dynamic statement, but not in the outside scope.  You can't change the db scope in such a way using dynamic sql.
0
 
LVL 6

Accepted Solution

by:
bcsql earned 300 total points
ID: 24171245
You can do something like this


/***************************************************/
DECLARE @STAGE VARCHAR(50) = 'IMANuclear_Stage_Tmp'
DECLARE @ODS VARCHAR(50) = 'IMANuclear_ODS_Tmp'
DECLARE @SQLCMD VARCHAR(300)
/***************************************************/
 
SET @SQLCMD = 'USE ' + @STAGE +  char(13) + 'select * from sysobjects'
EXEC (@SQLCMD)
GO

But it only changes the scope for that SQL statement.


0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Integration Management Part 2
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question