Share via

SSRS: Multi-valued Parameter as Stored Procedure Input

  • Article

Few months back I wrote an article on Multi-valued Parameter as Stored procedure Input, which will work only for single character multi-valued input parameter.

Now let us see an example for SSRS report with Multi-valued Parameter as Stored procedure Input, which will work in all cases.

Example:

Create Stored procedure in AdventureWorks database as shown in the following code block:

USE AdventureWorks2012 
GO 
 
CREATE PROCEDURE   Shift_Get 
@ShiftName NVARCHAR(500) 
AS
BEGIN
;WITH CTE1 
AS
( 
SELECT CAST(('<i><j>' + REPLACE(@ShiftName, ',',  '</j></i><i><j>') + '</j></i>'  )  AS  XML) CSV 
),CTE2 
AS
( 
SELECT CAST(i.query('./text()') AS   VARCHAR(100))CSV 
FROM CTE1  
CROSS APPLY CSV.nodes('/i/j') As   x(i) 
) 
SELECT * FROM HumanResources.Shift  
WHERE Name   IN (SELECT * FROM CTE2) 
END

 

Create Dataset pointing to this newly created Stored procedure in AdventureWorks database as shown below:

Create Dataset for Multi-valued input parameter as shown below:

Follow report parameter settings as shown in these images:

From Toolbox - > Report Items - > drag and drop Table on to the report body under Design pane
and then assign the column values with fields from dataset (Report_Dataset).

Now click on Preview pane, select the parameter values and click on View Report:

 

Note: Didn't make use of the SSRS JOIN function

See Also