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.

• calvo

  SSChampion

  Points: 12930

  More actions

  June 15, 2010 at 10:56 am

  #1180585

  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: 323522

  More actions

  June 15, 2010 at 11:01 am

  #1180595

  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

  June 15, 2010 at 2:43 pm

  #1180684

  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: 95461

  More actions

  June 15, 2010 at 5:33 pm

  #1180743

  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