stored procedure to insert into a table variable?

• cedar72

  Say Hey Kid

  Points: 711

  More actions

  January 21, 2005 at 8:18 am

  #68036

  Is it possible to use a stored procedure to insert into a table variable in layman's terms here is what I'm trying to do...

  Here is the first Proc

  ******************************************

  Create Procedure dbo.sp_Test

  as

  select 'Now','Then','Later'

  ******************************************

  Later call the first Proc in the second and populate a table

  ******************************************

  Create Procedure dbo.sp_Test2

  as

  declare @T table(when1 varchar(50),when2 varchar(50),when3 varchar(50));

  Insert Into @T

  Values(exec sp_Test)

  Select *

  from @T

• Nicholas Cain

  SSC-Dedicated

  Points: 33009

  More actions

  January 24, 2005 at 8:16 am

  #538755

  You cannot insert the data from an execute into a table variable, you could use a temp table instead.

  ---

  
  Shamless self promotion - read my blog http://sirsql.net

• Peter E. Kierstead

  SSCarpal Tunnel

  Points: 4114

  More actions

  January 27, 2005 at 9:38 am

  #539368

  Table variables are fast but they can only be used in the context in which they were Declared. They can't be used with:

  Select .. Into @TblVar

  Insert @TblVar Execute ...

  Passed to/from a batch via Exec or sp_ExecuteSQL

  Kind of limits their use...

  ---

  
  PeteK
  I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.