Global Temporary Tables and dynamic sql string

• thaitinh77

  Old Hand

  Points: 339

  More actions

  May 26, 2020 at 6:50 am

  #3757316

  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: 22999

  More actions

  May 26, 2020 at 1:02 pm

  #3757377

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