Is it possible to call an SP & return result into Table variable?

• Ian Main

  SSC Veteran

  Points: 233

  More actions

  October 26, 2005 at 5:57 pm

  #88340

  Hi,

  My basic question is: Is it possible (inside a scalar User defined function) to execute a stored proc and get its result set into a table variable?

  Let me explain what I'm doing and hopefully it will make more sense. When I run this code (ie execute SP apBL_String_Split and put its result set into a temporary table called #table - everything works fine) :

  DECLARE @csv as varchar(8000)

  SET @csv = '1,2,3,4,5'

  CREATE TABLE #table (ids smallint Primary Key, value varchar(8000))

  INSERT #table EXEC apBL_String_Split @csv, ','

  SELECT *

  FROM #table

  SELECT value as 'third' FROM #table WHERE ids = 2

  DROP TABLE #table

  The output is this (and this is normal and expected):

  (5 row(s) affected)

  ids    value

  ------ --------------------------------

  0      1

  1      2

  2      3

  3      4

  4      5

  (5 row(s) affected)

  third

  ----------------------------------------

  3

  (1 row(s) affected)

  So far so good.

  But when I put this code in my UDF I get the error "Cannot access temporary tables from within a function."

  So I think, OK I'll use a Table variable instead of a temporary table. But how do I get the output of the SP (apBL_String_Split) into the Table variable.

  The closest code I've got is this but it doesn't compile as and gives error "EXECUTE cannot be used as a source when inserting into a table variable.".

  ALTER FUNCTION dbo.GetResultsDefinition

  (@strResultsDefn AS varchar(8000))

  RETURNS varchar(200)

  AS

  BEGIN

   DECLARE @csv as varchar(8000)

   SET @csv = '1,2,3,4,5'

   

   DECLARE @table TABLE(ids smallint Primary Key, value varchar(8000))

   INSERT @table EXEC apBL_String_Split @csv, ','

   RETURN

   ('something')

  END

  Thanks in advance for any advice,

  Chiz.

• Kory Becker

  Hall of Fame

  Points: 3585

  More actions

  October 26, 2005 at 6:25 pm

  #600433

  Both activities a temp table in a UDF and the population of a temp table variable from and exec proc are not allowed temp (The errors say it all).  Why do you want to do this in a function?

• AJ Ahrens

  SSC-Insane

  Points: 20676

  More actions

  October 26, 2005 at 7:46 pm

  #600438

  If you are trying to use the UDF like a table why not use something like SELECT * FROM dbo.udf_MyTableFunction?

  
  
  Good Hunting!

  AJ Ahrens
  
  
  webmaster@kritter.net

• Farrell Keough

  SSCoach

  Points: 17414

  More actions

  October 26, 2005 at 7:47 pm

  #600439

  Have your function return a recordset.  Then call it from within the SP.  (Don't put your table variable within the function).  I have not done this with a @Table Variable, but I have with a #Temp Table...

  -- Within Stored Procedure...

  CREATE TABLE #Table( ids smallint Primary Key, value varchar(8000))

  INSERT INTO #Table

  SELECT * FROM dbo.GetResultsDefinition( @strPassedIn)

   

  I wasn't born stupid - I had to study.