@tmp variable table vs #tmp table

• Roham

  SSC Rookie

  Points: 25

  More actions

  December 9, 2004 at 5:26 pm

  #87237

  Does any one know why the first statement works and the second statement does not work?

  It gives me an error message

  Server: Msg 197, Level 15, State 1, Line 13

  EXECUTE cannot be used as a source when inserting into a table variable.

  create table #tmp (

   col1  int,

   col2  varchar(100)

  )

  declare @tmp table (

   col1  int,

   col2  varchar(100)

  )

  --First Insert

  insert into #tmp exec MyStoredProcedure

  select * from #tmp

  drop table #tmp

  insert into @tmp exec MyStoredProcedure

  select * from @tmp

   

   

• Antares686

  SSC Guru

  Points: 125444

  More actions

  December 9, 2004 at 6:50 pm

  #533270

  The error says it all.

  "EXECUTE cannot be used as a source when inserting into a table variable."

   

  so you cannot exec a stored procedure and get the results dumped into a table variable. Temp tables are not limited by that condition.

• Steve Jones - SSC Editor

  SSC Guru

  Points: 740606

  More actions

  December 10, 2004 at 10:23 am

  #533381

  Suspect that Antares is correct.