时间戳

首页 > 解决方案 > 从 SQL 函数返回动态透视表的困难 - “必须声明表变量 xxx”

sql - 从 SQL 函数返回动态透视表的困难 - “必须声明表变量 xxx”

问题描述

摘要：

我的存储过程在运行时返回“必须声明表变量“@InitialData”。

详情：

我表中的数据如下所示：

代码 日期 价值
001 2020 年 1 月 1 日 1,500 美元
001 2-1-2020 3,500 美元
002 2020 年 1 月 1 日 500 美元
002 2-1-2020 7000 美元

我的目标是像这样旋转数据，并将其返回给应用程序。

代码 2020 年 1 月 1 日 2-1-2020
001 1,500 美元 3,500 美元
002 500 美元 7000 美元

为了进一步过滤返回的数据，我使用了一个函数来返回一些名为ReturnEVMTotals的查询数据，它接受三个参数：

这些信息都与手头的任务没有直接关系，但我认为它应该有助于理解这个存储过程：

sp_ReturnPivotTotals 
    @intUProj int, 
    @dateStatus date,
    @strScale varchar(10)

DECLARE @InitialData TABLE 
        (
        intProjectID int, 
        strCC varchar(50), 
        decAmount decimal(18,2), 
        DateType varchar(50)
        )

    INSERT INTO @InitialData 
        SELECT intProjectID, strCC, decAmount, DateType FROM [dbo].[ReturnEVMTotals] (@intUProj,@dateStatus,@strScale)
    
    DECLARE @Columns as VARCHAR(MAX)
        SELECT @Columns =
        COALESCE(@Columns + ', ','') + QUOTENAME(DateType)
        FROM
           (SELECT DISTINCT DateType
            FROM   @InitialData
           ) AS B
        ORDER BY DateType

DECLARE @ReturnTable VARCHAR(MAX)

SET @ReturnTable = 'SELECT * FROM @InitialData  AS SourceTable
    PIVOT (
        MAX(decAmount)
        FOR DateType IN ('+@Columns+')
    ) AS PivotTable'

EXEC(@ReturnTable)

END

当我尝试运行该过程时，我收到以下错误：

消息 1087，级别 15，状态 2，行 3 必须声明表变量“@InitialData”。

我已经设法完成了很长一段时间的工作，没有提出任何问题，只是在谷歌上搜索我不知道的信息。这让我完全难住了。

请帮忙，谢谢。

标签： sqlsql-serverstored-procedurespivot-tablestored-functions

解决方案

首先让我们澄清你的错误的原因。这是因为在动态语句中您没有声明或定义变量@InitialData。它仅在动态语句之外定义，但变量仅在您定义它们的范围内可用，因此您无法使用它。

如果必须使用表变量，则需要先定义表类型，然后使用该参数。

首先，创建类型：

CREATE TYPE dbo.InitialData AS table (ProjectID int,        --Don't prefix your column names with the data type,
                                      CC varchar(50),       --it isn't needed. It also doesn't make a lot of sense
                                      Amount decimal(18,2), --when you name the last column in this table as DateType
                                      DateType varchar(50));--but it's not a date, it's a varchar. That's confusing.

然后你的 proc 看起来像这样：

CREATE PROC dbo.ReturnPivotTotals  --Removed sp_ prefix
    @intUProj int, 
    @dateStatus date,
    @strScale varchar(10)
AS --This was missing
BEGIN --This was also missing as you had a END
    DECLARE @InitialData dbo.InitialData

    INSERT INTO @InitialData 
    SELECT intProjectID, strCC, decAmount, DateType FROM [dbo].[ReturnEVMTotals] (@intUProj,@dateStatus,@strScale);
    
    DECLARE @Columns as NVARCHAR(MAX)
        SELECT @Columns =
        COALESCE(@Columns + ', ','') + QUOTENAME(DateType)
        FROM
           (SELECT DISTINCT DateType
            FROM   @InitialData
           ) AS B
        ORDER BY DateType

DECLARE @ReturnTable NVARCHAR(MAX)

    SET @ReturnTable = 'SELECT * FROM @InitialData  AS SourceTable
    PIVOT (
        MAX(decAmount)
        FOR DateType IN ('+@Columns+')
    ) AS PivotTable'

    EXEC sys.sp_executesql @ReturnTable, N'@InitialData dbo.InitialData READONLY', @InitialData;

END
GO

或者，您可以使用临时表，该表将在内部范围内公开：

CREATE PROC dbo.ReturnPivotTotals  --Removed sp_ prefix
    @intUProj int, 
    @dateStatus date,
    @strScale varchar(10)
AS --This was missing
BEGIN --This was also missing as you had a END
    CREATE TABLE #InitialData (ProjectID int,        --Again, don't prefix your column names with the data type.
                               CC varchar(50),       
                               Amount decimal(18,2), 
                               DateType varchar(50));
    INSERT INTO @InitialData 
    SELECT intProjectID, strCC, decAmount, DateType FROM [dbo].[ReturnEVMTotals] (@intUProj,@dateStatus,@strScale);
    
    DECLARE @Columns as NVARCHAR(MAX)
        SELECT @Columns =
        COALESCE(@Columns + ', ','') + QUOTENAME(DateType)
        FROM
           (SELECT DISTINCT DateType
            FROM  #InitialData
           ) AS B
        ORDER BY DateType

DECLARE @ReturnTable NVARCHAR(MAX)

    SET @ReturnTable = 'SELECT * FROM #InitialData  AS SourceTable
    PIVOT (
        MAX(decAmount)
        FOR DateType IN ('+@Columns+')
    ) AS PivotTable'

    EXEC sys.sp_executesql @ReturnTable;

END
GO

不过说实话，你需要表变量/临时表吗？假设您的函数是一个编写良好的内联表值函数，我认为它的性能非常好并且可以快速返回数据。此外，正如Charlieface 提到的，不要使用无证行为；不能保证语法会起作用。而是使用FOR XML PATHor STRING_AGG。我将FOR XML PATH在这里使用，因为我们不知道您的 SQL Server 版本：

CREATE PROC dbo.ReturnPivotTotals  --Removed sp_ prefix
    @UProj int, --Don't prefix your variable names with the data type either
    @Status date,
    @Scale varchar(10)
AS --This was missing
BEGIN --This was also missing as you had a END
    
    DECLARE @Columns NVARCHAR(MAX);
    SET @Columns = STUFF((SELECT N',' + QUOTENAME(DateType)
                          FROM [dbo].[ReturnEVMTotals] (@UProj,@Status,@Scale)
                          GROUP BY DateType
                          ORDER BY DateType
                          FOR XML PATH(N''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,1,'');

    DECLARE @ReturnTable NVARCHAR(MAX)

    SET @ReturnTable = 'SELECT *
    FROM [dbo].[ReturnEVMTotals] (@UProj,@Status,@Scale) AS SourceTable
    PIVOT (
        MAX(decAmount)
        FOR DateType IN ('+@Columns+')
    ) AS PivotTable'

    EXEC sys.sp_executesql @ReturnTable, N'@UProj int, @Status date, @Scale varchar(10)', @UProj, @Status, @Scale;

END
GO

推荐阅读