October 26, 2005 at 5:57 pm
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.
October 26, 2005 at 6:25 pm
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?
October 26, 2005 at 7:46 pm
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
October 26, 2005 at 7:47 pm
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply