使用表值函数或视图从存储过程返回结果集

Question

我正在使用 SQL Server 2000，并且对如何执行此操作感到困惑：

我有一个存储过程，它返回绑定到 .Net Web 应用程序中的 gridview 的单个结果集。

现在我想要一个“表”，这样我就可以在现有的 VB6 应用程序中使用它，如下所示：

SELECT * FROM myTable 

...其中“myTable”的架构反映了从此存储过程返回的结果集中的列。

这是一个有效的脚本：

CREATE TABLE #ResultSet (
    StateFIPS           CHAR(2)
    ,CountyFIPS         CHAR(3)
,StateName          VARCHAR(30)
,CountyName         VARCHAR(40)
,MostRecentData_P       VARCHAR(20)
,PData              VARCHAR(3)
,AvgNbrMtgPerMonthInLastYear_P  INT
,MostRecentData_R       VARCHAR(20)
,RData              VARCHAR(3)
,AvgNbrMtgPerMonthInLastYear_R  INT
,MostRecentData_FHA     VARCHAR(20)
,MostRecentData_VA      VARCHAR(20)
)
INSERT INTO #ResultSet
EXECUTE dbo.FetchCoverageByState_V2 
SELECT * FROM #ResultSet

我试图将其放入视图中，但遇到错误：

"Views or functions are not allowed on temporary tables"

我还尝试了这样的函数：

create function dbo.udfCoverages() 

returns @ResultSet table (
    StateFIPS   CHAR(2)
    ,CountyFIPS CHAR(3)
,StateName  VARCHAR(30)
,CountyName VARCHAR(40)
,PData      VARCHAR(3)
,RData      VARCHAR(3)  
) as
INSERT @ResultSet (
    StateFIPS           CHAR(2)
    ,CountyFIPS         CHAR(3)
,StateName          VARCHAR(30)
,CountyName         VARCHAR(40)
,MostRecentData_P       VARCHAR(20)
,PData              VARCHAR(3)
,AvgNbrMtgPerMonthInLastYear_P  INT
,MostRecentData_R       VARCHAR(20)
,RData              VARCHAR(3)
,AvgNbrMtgPerMonthInLastYear_R  INT
,MostRecentData_FHA     VARCHAR(20)
,MostRecentData_VA      VARCHAR(20)
)
EXECUTE dbo.FetchCoverageByState_V2  
return

在 UDF 尝试中，我在 INSERT 附近遇到语法错误，我想知道 SQL Server 是否2000年支持这一点。

你会推荐什么？

编辑更新（根据下面 Ray 的第一个建议）：

create function dbo.udfCoverages() 
returns @ResultSet table (
    StateFIPS   CHAR(2)
    ,CountyFIPS CHAR(3)
,StateName  VARCHAR(30)
,CountyName VARCHAR(40)
,PData      VARCHAR(3)
,RData      VARCHAR(3)  
) as
BEGIN   
INSERT @ResultSet 
(
         StateFIPS          CHAR(2)
        ,CountyFIPS         CHAR(3)
    ,StateName          VARCHAR(30)
    ,CountyName         VARCHAR(40)
    ,MostRecentData_P       VARCHAR(20)
    ,PData              VARCHAR(3)
    ,AvgNbrMtgPerMonthInLastYear_P  INT
    ,MostRecentData_R       VARCHAR(20)
    ,RData              VARCHAR(3)
    ,AvgNbrMtgPerMonthInLastYear_R  INT
    ,MostRecentData_FHA     VARCHAR(20)
    ,MostRecentData_VA      VARCHAR(20)
)
    EXECUTE dbo.FetchCoverageByState_V2 
    return
END

这里我得到：第 19 行：“CHAR”附近的语法不正确。注意：第 19 行是上面 INSERT 之后的 2 行。

Answer 1

尝试使用 OPENROWSET，如本问题所示。我以为我曾经在博客上谈论过它，但也许没有。

将存储过程的结果插入临时表

记得给亚伦的回答点赞。

Answer 2

无论您做什么，都无法将过程转换为表值函数或视图。对于函数允许做什么和不允许做什么有严格的限制。由于 TVF 或视图可以与任何其他语句组合，例如作为子查询出现在 SELECT 中，或者作为 UPDATE/INSERT/DELETE 的一部分等等，因此对函数施加了某些行为限制，特别是当涉及到执行副作用时。另一方面，程序子却像小鸟一样自由。

您唯一能做的就是使用 INSERT ... EXEC ... 将过程的输出捕获到表中，仅此而已，还有额外的限制，即不能有另一个 INSERT ... EXEC ... 嵌套。

您发布的所有内容都表明 fetchCcoverageByState_V2 必须是表值函数，这是您应该追求的唯一途径。

Answer 3

您需要将 begin 和 end 包裹在函数体中（在 as 之后）