Post reply

use clause + dynamic sql

  • April 3, 2008 at 6:06 pm

    #69764

    Anyone know why is not working?

    declare @sql varchar(4000), @db sysname

    set @db = 'master'

    set @sql = 'USE ' + @db

    exec (@sql)

  • April 3, 2008 at 6:23 pm

    #798167

    Because you can't change context like that. Can't do it in a procedure either. Basically, "USE xyz db" only works in a query editor.

  • April 3, 2008 at 10:26 pm

    #798196

    How do i fix my code?

  • April 3, 2008 at 11:42 pm

    #798205

    The [font="System"]USE db[/font] command only changes the session it was executed in. [font="System"]EXEC(@sql)[/font] does not execute the [font="System"]@sql [/font]text in your current session, but creates a new session and executes it there, then it exits back to your original session.

    So you cannot use dynamic SQL to change your current session, only that Temporary session that goes away after the string execute.

    So, how can you fix it? Easy (but messy). After you make your [font="System"]@sql[/font] string with the [font="System"]USE [/font]command, then add to the [font="System"]@sql[/font] string, all of the other SQL commands that you were going to execute. Now when you execute that temporary session, it will also do all of the other work there also.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • April 4, 2008 at 9:57 am

    #798554

    Beautiful.

    Thanks.

  • April 6, 2008 at 12:09 am

    #798835

    glad I could help.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

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

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