Temporary tables SCOPE

• Carlo Romagnano

  SSC-Insane

  Points: 22749

  More actions

  September 26, 2017 at 8:41 am

  #1960965

  Christian4143 - Tuesday, September 26, 2017 7:54 AM

  Just to add:

  There is different behavior on SQL-Server 2012. I have to change the statement to (there is no 'if exists')

  
if object_id ('tempdb..#t') is not null drop table #t
  execute ('create table #t (i int, z int)')
  select * from #t
  GO
  -- second Batch
  if object_id ('tempdb..#t') is not null drop table #t
  create table #t (i int)
  execute ('alter table #t add z int')
  select * from #t
  

  There is an error on SQL-Server 2012:

  Msg 208, Level 16, State 0, Line 3
  Invalid object name '#t'.

  The resut will be i,z.

  Read the question! The first batch returns exactly that error!

• Christian4145

  SSC Rookie

  Points: 36

  More actions

  September 26, 2017 at 8:51 am

  #1960968

  Carlo Romagnano - Tuesday, September 26, 2017 8:41 AM

  Christian4143 - Tuesday, September 26, 2017 7:54 AM

  Just to add:

  There is different behavior on SQL-Server 2012. I have to change the statement to (there is no 'if exists')

  
if object_id ('tempdb..#t') is not null drop table #t
  execute ('create table #t (i int, z int)')
  select * from #t
  GO
  -- second Batch
  if object_id ('tempdb..#t') is not null drop table #t
  create table #t (i int)
  execute ('alter table #t add z int')
  select * from #t
  

  There is an error on SQL-Server 2012:

  Msg 208, Level 16, State 0, Line 3
  Invalid object name '#t'.

  The resut will be i,z.

  Read the question! The first batch returns exactly that error!

  sorry for that.

  Learning buy doing.