Post reply

Need help creating dynamic "USE" statement... Thanks.

  • April 26, 2004 at 4:17 pm

    #111060

    Howdy,

    I know I've seen it somewhere on this forum and just can't find it again...  somebody please tell me how to create a dynamic "USE" statement for inclusion as a parameter of a stored procedure?

    Here's what I tried and it executes without error but does not change to the listed db.

    DECLARE @MyDB nVARCHAR(1000)

    DECLARE @MySQL nVARCHAR(1050)

    SET @MyDB = N'Master'

    SET @MySQL = N'USE '+ @MyDB + CHAR(13)

    PRINT @MySql

    EXEC sp_executesql @MySql

    I'm running "Standard Edition 2000".

    Thanks for the help...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • April 26, 2004 at 8:50 pm

    #504283

     

    Try:

     

    DECLARE @MyDB nVARCHAR(1000)

    DECLARE @MySQL nVARCHAR(1050)

    SET @MyDB = N'Master'

    SET @MySQL = N'USE '+ @MyDB + ';'

    PRINT @MySql

    EXEC @MySql

  • April 26, 2004 at 11:10 pm

    #504284

    Got the same result... no error but no change.  Thank you for trying.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • April 27, 2004 at 9:05 pm

    #504414

    Unfortunately I do not have SQL on my home PC.

    I remember trying this and, sure it worked:

    DECLARE @MyDB nVARCHAR(1000)

    DECLARE @MySQL nVARCHAR(1050)

    SET @MyDB = N'Master'

    SET @MySQL = N'USE '+ @MyDB + ';' +'select db_name()'

    PRINT @MySql

    EXEC (@MySql)

  • April 28, 2004 at 3:06 am

    #504454

    Don't forget that the use-statement is only valid during the execution of the exec-command.

    -> Add anything you want to be done to the string @MySQL

    Best regards
    karl

  • April 28, 2004 at 3:17 am

    #504455

    Having fun with more dynamic dynamic sql

    DECLARE @stmt NCHAR(1000)

    SET @stmt = 'USE Frank_3 ' + CHAR(13) + 'DECLARE @stmt NCHAR(1000)' + CHAR(13) +

    'SET @stmt = ''CREATE TABLE Second (col_a int)''' + CHAR (13) +

    'EXEC sp_executesql @stmt'

    EXEC sp_executesql @stmt

    Do I need to add the reference to Erland Sommarskog again?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

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

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