从 SQL 函数返回动态数据透视表的困难 - "Must Declare the table variable xxx"

Question

摘要：

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

详细信息：

我表格中的数据如下：

<1500美元>

代码		日期	值
001	1-2020
001	2-2020	$3500
002	1-2020	500美元
002	2-1-2020	$7000

首先，让我们澄清错误的原因。这是因为在动态语句中，您没有声明或定义变量@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 PATH或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