Passing Comma Separated Values (SSRS - Multi-valued Parameter) as Input to Stored Procedure

Passing Comma Separated Values (SSRS - Multi-valued Parameter) as Input to Stored Procedure

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.

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'

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

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,'

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 mulivalued parameter is this query:
SELECT Id FROM Employee_Detail





So to pass multi-valued parameter in SSRS as input to 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 stored procedure.
Sort by: Published Date | Most Recent | Most Useful
Comments
  • good job

  • Kindly note that the above suggested method in this post will work only for single character multi-valued parameter :)

  • CHARINDEX is a poor performing solution and also why we're only using VARCHAR(20)? How many Ids will fit into VARCHAR(20)?

  • Hi Naomi ,

    Thanks for your suggestion , I had given sizing considering this example , here there is only 5 Ids .

  • Hi Naomi ,

    Thanks for your suggestion , I had given sizing considering this example , here there is only 5 Ids .

  • Hi Naomi,

    Thanks for your valuable suggestion , I had given sizing , considering this example - here there are only 5 Ids

Page 1 of 1 (6 items)