Use Database statement not working in an EXEC statement

Question

Use Database statement not working in an EXEC statement

markjrouse

Mr or Mrs. 500

Points: 512
More actions
June 15, 2010 at 10:19 am

#236451

I was wondering if any one could tell me why the following does not work, and the Database is not switched to the database name I set as @CaseName
DECLARE @CaseName nvarchar(256)
Set @CaseName = 'Test'
.........
EXECUTE ('USE ['+@CaseName+']')
-- ******************************************************
-- Create database schemas
-- ******************************************************
PRINT ''
PRINT '*** Creating Database Schemas'
GO
CREATE SCHEMA [RawData] AUTHORIZATION [dbo]
GO
CREATE SCHEMA [Artifacts] AUTHORIZATION [dbo]
GO
The EXECUTE ('USE ['+@CaseName+']') statement dos not work, and will not change to my Test database to create the schemas on Test.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

calvo SSChampion Points: 12930 More actions · Answer 1

With the fear of answering this "wrong", I'll throw it out there anyway.

When I try to use a database dynamically, it's usually within a procedure. Then I can use dynamic SQL to do what I need it to do.

Here's a quick piece of code that can maybe help you with your situation.

declare @db varchar(20)

declare @sql varchar(50)

set @db = 'test'

set @sql = 'use [' + @db + ']; select * from table1'

exec (@sql)

I'm anxious to see how some of the experts handle this too.

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

Lowell SSC Guru Points: 323518 More actions · Answer 2

calvo's example is exactly correct, i think; the EXEC() statement is stand alone, and does not affect the session/window it is a part of.

so to do what you want, you have to switch to dynamic sql, just as calvo demoed; i think this is what you are after specifically:

declare @sql varchar(50)

set @sql = 'use [' + +@CaseName + ']; CREATE SCHEMA [RawData] AUTHORIZATION [dbo];CREATE SCHEMA [Artifacts] AUTHORIZATION [dbo];'

exec (@sql)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

george sibbald SSC Guru Points: 104210 More actions · Answer 3

use db statement and other statements need to be in same exec for change of db context to work

e.g.

set @statement = N'use [' + @name +']'+char(13)+N'exec sp_helpdb [' + @name +']'

exec sp_executesql @statement

---------------------------------------------------------------------

WayneS SSC Guru Points: 95460 More actions · Answer 4

Did you look at BOL? Specifically, read the section "Using EXECUTE with a Character String".

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2