Post reply

Use Database statement not working in an EXEC statement

  • 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.

  • 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.

  • 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!

  • 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

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

  • 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

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

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