Pass Int array to SQL Server stored procedure for a Select statement

Question

I have a SQL Server Reporting Services report that runs from a stored procedure. I want to send a parameter from the report to this stored procedure to be filtered on. The parameter should be an array of int. Please advise how this is done? How do you send an array of Int? The stored procedure so far:

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

Calling the stored procedure from a query works, however sending an array is the problem:

DECLARE @ProductiveUnitID int
SET @ProductiveUnitID = (44)

EXEC [dbo].[CostDriverIdentification] @ProductiveUnitID

score 1 · Answer 1 · answered Dec 14 '20 at 23:00

You can't send an array of an integer to a stored procedure from SSRS.

Instead, use a string with the parameter's numbers. Use pipes to delimit the numbers

Pass the value from SSRS report with the Parameter Value:

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

This will create a string like

|1|2|3|4|

Then check for the IDs using CHAR_INDEX in the WHERE:

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

This will add pipes around the ID and check in the string parameter for the comparison:

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

If the ID is not found, a zero is returned by CHARINDEX, making the AND clause FALSE.

How to pass an array into a SQL Server stored procedure

Pass Int array to SQL Server stored procedure for a Select statement

1 Answers1