SSRS: Passing CSV as Input to Stored Procedure

Article
1/17/2024

In this post let us see how we can easily handle passing comma separated values as input to the stored procedure. This kind of situation generally happens when we design report with multi-valued input parameter which will be passed as input to the stored procedure.

Code block

Sample data

This code block has the sample data:

USE AdventureWorks2012 
GO 
  
CREATE TABLE  Employee_Detail(Id VARCHAR(20),Name VARCHAR(60),Designation VARCHAR(80),Salary BIGINT) 
GO 
  
INSERT Employee_Detail SELECT 1,'Sathya','VP','10000'
INSERT Employee_Detail SELECT 2,'Sunny','Manager','4000'
INSERT Employee_Detail SELECT 3,'Deepak','Senior Programmer','2500'
INSERT Employee_Detail SELECT 4,'Lexi','Programmer','1000'
INSERT Employee_Detail SELECT 5,'Sasha','Programmer','1000'

Stored procedure

The code block listed below has the sample stored procedure which accepts the input as comma separated values:

USE AdventureWorks2012 
GO 
  
CREATE PROCEDURE  Get_EmployeeDetails 
@EmpIds VARCHAR(20) 
AS
BEGIN
  
SELECT * FROM Employee_Detail WHERE CHARINDEX(Id,@EmpIds) > 0 
  
END

Execution Statement

This code block has the sample execution statements of that stored procedure:

EXEC Get_EmployeeDetails '1,2'
EXEC Get_EmployeeDetails '3,4,5'
EXEC Get_EmployeeDetails ',2,'

Sample

The following screenshots will show an example of passing multivalued parameter in SSRS as input to the stored procedure. In SSRS, the stored procedure Get_EmployeeDetails is the dataset for table and the dataset for multi-valued parameter is this query:

SELECT Id FROM Employee_Detail

So to pass multi-valued parameter in SSRS as input to a stored procedure, we need to make use of JOIN function in SSRS and then split using split function in SQL Server. In this post using CHARINDEX inside stored procedure, we can easily handle comma separated input to a stored procedure.

Share via