Post reply

exec(sql statement) command confusion

  • November 29, 2007 at 11:29 am

    #201362

    I am trying to do populate a table with information from a different database and I'm running into problems. The concept seems pretty simple to me, unfortunately the execution is escaping me. Here is the code I wrote:

    declare @name1 varchar(60), @count1 int, @server1 varchar(60), @sqlstmt varchar(1000)

    select @server1 = 'SQLserver'

    select @sqlstmt = 'declare @count1 int; select @count1 = count(name) from ' +@server1+ '.master.sys.sysdatabases; --print @count1'

    exec(@sqlstmt)

    print @count1

    insert test1.dbo.ServerCount values (@server1, @count1)

    What is confusing me is why do I need to re-declare the variable @count1 and why the output from the exec command can not be used in the rest of the code? If I remove the comment (--) from the print@count1, it displays the correct answer with the exec(@sqlstmt) command, but the following print command returns null. Any help would be greatly appreciated.

  • November 29, 2007 at 11:45 am

    #755599

    I know thats not possible, but I don't know the reason for it.

    So to get around that you can do this:

    declare @name1 varchar(60), @count1 int, @server1 varchar(60), @sqlstmt varchar(1000)

    select @server1 = 'SQLserver'

    select @sqlstmt = ' insert test1.dbo.ServerCount select ''+@server1 +'', count(name) from ' +@server1+ '.master.sys.sysdatabases;

    exec(@sqlstmt)

  • November 29, 2007 at 12:56 pm

    #755646

    It is possible to populate a variable in such a way, but you'll need to use sp_ExecuteSQL... please see Books Online for the example of how.

    The reason why it didn't work the first way you tried it is because the @Count1 within the dynamic SQL is in a different "scope" than the @Count1 outside the dynamic SQL. sp_ExecuteSQL allows the connection between the two when properly configured.

    --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)

Viewing 3 posts - 1 through 2 (of 2 total)

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