How to call a stored procedure in a table function in SQL Server 2005?

Question

I want to call a Stored Procedure that returns a table in a Value Table. How to create a table function?

For example: "sp_GetTable" is a stored procedure. It returns a table. Here I want to write one function called "fn_GetTable"

I want to get the same result that was obtained using the stored procedure.

Here is my stored procedure:

ALTER PROC sp_GetTable AS BEGIN ------------ Creating TempTable(#MasterTable) ---------------------- CREATE TABLE #MasterTable (ItemID INT, ItemName VARCHAR(100), Specifications VARCHAR(100), D1 VARCHAR(50), D2 VARCHAR(50), D3 VARCHAR(50), D1_Code VARCHAR(50), D2_Code VARCHAR(50), D3_Code VARCHAR(50)) ------------ Creating TempTable(#TempItems) ---------------------- CREATE TABLE #TempItems(ItemID INT, ItemName VARCHAR(100), Specifications VARCHAR(100), D1 VARCHAR(50), D2 VARCHAR(50), D3 VARCHAR(50), D1_Code VARCHAR(50), D2_Code VARCHAR(50), D3_Code VARCHAR(50)) ------------ Creating & Inserting TempTable(@Dim) ---------------------- DECLARE @Dim TABLE (DCodes VARCHAR(100)) INSERT INTO @Dim SELECT D1_Code FROM MAS_SizeType WHERE D1_Code <> '' INSERT INTO @Dim SELECT D2_Code FROM MAS_SizeType WHERE D2_Code <> '' INSERT INTO @Dim SELECT D3_Code FROM MAS_SizeType WHERE D3_Code <> '' ------------ Inserting data into TempTable(#MasterTable) ---------------------- INSERT INTO #MasterTable SELECT STR_Item.ItemID, STR_Item.ItemName, STR_Item_Specifications.SpecificationName, STR_Item.D1, STR_Item.D2, STR_Item.D3, MAS_SizeType.D1_Code, MAS_SizeType.D2_Code, MAS_SizeType.D3_Code FROM STR_Item INNER JOIN MAS_SizeType ON STR_Item.SizeTypeID = MAS_SizeType.SizeTypeID INNER JOIN STR_Item_Specifications ON STR_Item.SpecificationID = STR_Item_Specifications.SpecificationID -------------------- Inserting Data into #TempItems Table ---------------------------- INSERT INTO #TempItems SELECT * FROM #MasterTable ------------------Cursor for All dimensions details into single row for each items ---------------------- DECLARE @DCode VARCHAR(MAX), @Cnt INT SET @Cnt = 0 DECLARE ColAdd CURSOR FOR SELECT DISTINCT DCodes FROM @Dim OPEN ColAdd FETCH NEXT FROM ColAdd INTO @DCode WHILE (@@FETCH_STATUS = 0) BEGIN SET @Cnt = 1 EXECUTE ('ALTER TABLE #TempItems ADD ' + @DCode + ' VARCHAR(50)') EXECUTE('UPDATE #TempItems SET [' + @DCode + '] = M.D1 FROM #MasterTable M INNER JOIN #TempItems T ON T.ItemID = M.ItemID AND M.D1_Code = ''' + @DCode + '''') EXECUTE('UPDATE #TempItems SET [' + @DCode + '] = M.D2 FROM #MasterTable M INNER JOIN #TempItems T ON T.ItemID = M.ItemID AND M.D2_Code = ''' + @DCode + '''') EXECUTE('UPDATE #TempItems SET [' + @DCode + '] = M.D3 FROM #MasterTable M INNER JOIN #TempItems T ON T.ItemID = M.ItemID AND M.D3_Code = ''' + @DCode + '''') FETCH NEXT FROM ColAdd INTO @DCode END CLOSE ColAdd DEALLOCATE ColAdd IF (@Cnt = 1) BEGIN SELECT @DCode = STUFF(( SELECT DISTINCT '],[' + ltrim(DCodes) FROM @Dim ORDER BY '],[' + ltrim(DCodes) FOR XML PATH('') ), 1, 2, '') + ']' EXECUTE ('SELECT ItemID, ItemName, Specifications, ' + @DCode + ' FROM #TempItems ORDER BY ItemName') END DROP TABLE #TempItems DROP TABLE #MasterTable END 

Answer 1

It is possible to do this from the Scalar Function, it is impossible to do it from the Value Table function. Scalar-valued functions return a single data type value and cannot return tables either. Since you cannot select from a stored procedure (you need EXEC ), this is not the place for this function in the table.

In general, if a stored procedure returns a, single, result set, define a table function. If the stored procedure calculates a scalar value, define a scalar function.

From: Rewriting stored procedures as functions

Re-writing the stored procedure as a value table will result in maximum performance.

Answer 2

Impossible to do. You must rewrite your SP as UDF.

Edit

There seem to be really ugly workarounds using openquery . But I would recommend rewriting your SP into a function, then you can call a new function from the old SP (if you need to)