SQL Server 2005中如何调用表值函数中的存储过程？

How to call the Stored Procedure in the Table Valued Function in SQL Server 2005?

提问by thevan

I want to call the Stored Procedure which returns a table in the Table Valued Function. How to create the Table Valued Function?

我想调用存储过程，它在表值函数中返回一个表。如何创建表值函数？

For Example: "sp_GetTable" is the Stored procedure. It returns a table. Here, I want to write one function called "fn_GetTable"

例如：“sp_GetTable”是存储过程。它返回一个表。在这里，我想写一个名为“fn_GetTable”的函数

I want to get the same result which has given by 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

采纳答案by Codesleuth

It's possible to do this from within a Scalar Function,not possible to do from a Table Valued 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 to EXEC) there is no place for this in a table-valued function.

这可以从一个标量函数内做到这一点，不可能从一个表值函数来完成。标量值函数返回单个数据类型值，也不能返回表。由于您无法从存储过程中选择（您需要EXEC），因此在表值函数中没有位置。

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

通常，如果存储过程返回单个结果集，则定义一个表值函数。如果存储过程计算标量值，则定义标量函数。

From: Rewriting Stored Procedures as Functions

来自：将存储过程重写为函数

Re-writing the Stored Procedure as a Table-Valued Function will yield you the most performance.

将存储过程重写为表值函数将为您带来最大的性能。

回答by Magnus

Cant be done. You need to rewrite your SP to be a UDF.

做不到。您需要将 SP 重写为 UDF。

Edit

编辑

Apparently there are some really ugly workaroundsusing openquery. But I would recommend rewriting your SP to a function, then you can call the new function from the old SP (if you need to)

显然，有一些真正丑陋的解决方法使用openquery。但我建议您将 SP 重写为函数，然后您可以从旧 SP 调用新函数（如果需要）