将Int数组传递给Select语句的SQL Server存储过程

Question

我有一个从存储过程运行的SQL Server Reporting Services报告。我想将报表中的一个参数发送到此存储过程中进行筛选。该参数应该是int的数组。请说明如何完成？如何发送Int数组？到目前为止的存储过程：

ALTER PROCEDURE [dbo].[CostDriverIdentification] 
@ProductiveUnitID Int
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CountPUIDs int
SET @CountPUIDs = COUNT(@ProductiveUnitID)
SELECT @CountPUIDs
SELECT 
dbo.View_ProdUnit.ProdUnitID, dbo.View_ProdUnit.ProdUnitNo, 
dbo.View_ProdUnit.PU_Text, dbo.View_ProdUnit.PUGroup, 
dbo.View_ProdUnit.CompanyID, dbo.View_ProdUnit.UserGroupID, 
dbo.View_PivotWOCCCost.ComponentCodeID, 
dbo.ComponentCode.C_Code + ' - ' + dbo.ComponentCode.CC_Text AS ComponentCode, 
dbo.View_PivotWOCCCost.ActivityTypeID AS WO_AT, 
dbo.ActivityType.ATCode, dbo.View_PivotWOCCCost.ForecastItemID, 
dbo.View_FCI_LCM.FCI_text, dbo.Package.PackageNo, 
dbo.View_PivotWOCCCost.CountOfWONumber, 
ROUND(dbo.View_PivotWOCCCost.AvgWOCost, 2) AS AvgWOCost,  
ROUND(dbo.View_PivotWOCCCost.TotSpend, 2) AS TotSpend, 
dbo.View_FCI_LCM.LCMName, dbo.View_FCI_LCM.LCMItemID, 
dbo.View_FCI_LCM.First, dbo.View_FCI_LCM.Frequency, 
dbo.View_FCI_LCM.PartsCost, dbo.View_FCI_LCM.LabourCost, 
dbo.View_FCI_LCM.MiscCost, dbo.View_FCI_LCM.TotalCost, 
dbo.View_ProdUnit.ProdUnitLifeLeft, dbo.View_ProdUnit.ProdUnitLifeUsed, 
dbo.View_ProdUnit.ProdUnitLifeUsed / dbo.View_PivotWOCCCost.CountOfWONumber AS ActualFrequency, 
ROUND(dbo.View_FCI_LCM.ItemRate, 2) AS LCMItemRate, 
ROUND(dbo.View_PivotWOCCCost.TotSpend / dbo.View_ProdUnit.ProdUnitLifeUsed, 2) AS ActItemRate, 
CASE 
WHEN View_FCI_LCM.ItemRate IS NULL 
THEN ROUND(View_PivotWOCCCost.TotSpend / View_ProdUnit.ProdUnitLifeUsed, 2) 
ELSE Round(Abs(View_FCI_LCM.ItemRate - View_PivotWOCCCost.TotSpend / View_ProdUnit.ProdUnitLifeUsed), 2) 
END AS RateVar, 
ParentCC.C_Code AS CompCodeCostGroup, 
ParentCC.CC_Text AS CompCodeCostGroup_Text, 
(dbo.View_ProdUnit.ProdUnitLifeUsed / dbo.View_PivotWOCCCost.CountOfWONumber ) / @CountPUIDs as AverageFreq
FROM     
dbo.Package 
INNER JOIN
dbo.View_PivotWOCCCost ON dbo.Package.ID = dbo.View_PivotWOCCCost.PackageID 
INNER JOIN
dbo.View_ProdUnit ON dbo.View_PivotWOCCCost.ProductiveUnitID = dbo.View_ProdUnit.ProdUnitID 
INNER JOIN
dbo.ComponentCode ON dbo.View_PivotWOCCCost.ComponentCodeID = dbo.ComponentCode.ID 
INNER JOIN
dbo.ActivityType ON dbo.View_PivotWOCCCost.ActivityTypeID = dbo.ActivityType.ATID 
LEFT OUTER JOIN
dbo.ComponentCode AS ParentCC ON dbo.ComponentCode.CC_CostGroupID = ParentCC.ID 
LEFT OUTER JOIN
dbo.View_FCI_LCM ON dbo.View_PivotWOCCCost.ForecastItemID = dbo.View_FCI_LCM.FCI_ID
WHERE  
(dbo.View_ProdUnit.ProdUnitLifeUsed <> '0') 
AND dbo.View_ProdUnit.ProdUnitID = @ProductiveUnitID
END

从查询中调用存储过程是可行的，但是发送数组是个问题：

DECLARE @ProductiveUnitID int
SET @ProductiveUnitID = (44)
EXEC [dbo].[CostDriverIdentification] @ProductiveUnitID

Answer 1

不能将整数数组从SSRS发送到存储过程。

相反，使用带有参数数字的字符串。使用管道分隔数字

将SSRS报告中的值与参数值一起传递：

="|" & JOIN(Parameters.INT.Value, "|") & "|" 

这将创建一个类似的字符串

|1|2|3|4|

然后在WHERE：中使用CHAR_INDEX检查ID

AND CHARINDEX('|' + CAST(ProdUnitID as varchar(50)) + '|', @ProductiveUnitID) > 0 

这将在ID周围添加管道，并检查字符串参数以进行比较：

AND CHARINDEX('|2|', '|1|2|3|4|') > 0 

如果找不到ID，CHARDINDEX将返回一个零，从而使AND子句为FALSE。

如何将数组传递到SQL Server存储过程