Global Temporary Tables and dynamic sql string

  • thaitinh77

    SSC Veteran

    Points: 279

    I have a script:

    if OBJECT_ID('temptable') is not null
    drop table temptable
    go
    if OBJECT_ID('tempdb.dbo.##t') is not null
    drop table ##t
    go

    create table temptable(EmployeeID varchar(50), ProjectID varchar(50))
    go

    declare @s varchar(4000)

    set @s = 'SELECT top 10 T.EmployeeID into ##t FROM temptable T'
    exec (@s)

    set @s = '
    drop table ##t
    SELECT top 10 T.EmployeeID, T.ProjectID into ##t FROM temptable T

    select ProjectID from ##t
    '
    exec (@s)

    if OBJECT_ID('temptable') is not null
    drop table temptable
    go
    if OBJECT_ID('tempdb.dbo.##t') is not null
    drop table ##t
    go

    With error:

    Msg 207, Level 16, State 1, Line 14

    Invalid column name 'ProjectID'.

    But I can replaced "select ProjectID from ##t" with "select * from ##t", then press F5 and I don't have error. Why ? I don't understand.

    Please help me, thanks all!

     

  • Neil Burton

    SSC-Insane

    Points: 22204

    I've done a bit of research and experimenting on this and I think I've worked out what's happening.  I've got a theory as to why but I shouldn't take as a fact until one of the big hitters has confirmed or otherwise.

    What I think is going on is that because the ProjectID column doesn't initially exist in the ##t table, the second set of @S doesn't compile even though the first statement is to DROP ##t.  When SELECT * is used, the batch compiles and runs as expected.

    There's a dmv that will show the metadata for the @S variable without executing it.  If you run the query below it will show the compile error in the last few columns.  If you change ProjectID for *, you'll see a lot more data in the dmv results.

    if OBJECT_ID('temptable') is not null
    drop table temptable
    go
    if OBJECT_ID('tempdb.dbo.##t') is not null
    drop table ##t
    go

    create table temptable(EmployeeID varchar(50), ProjectID varchar(50))
    go

    declare @s varchar(4000)

    set @s = 'SELECT top 10 T.EmployeeID into ##t FROM temptable T'
    exec (@s)

    set @s = '
    drop table ##t
    SELECT top 10 T.EmployeeID, T.ProjectID into ##t FROM temptable T

    select ProjectID from ##t
    '
    SELECT * FROM sys.dm_exec_describe_first_result_set(@s,null,0)

     

     


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

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

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