Resources For IT Professionals
Post an article
Wikis - Page Details
  • First published by (8Microsoft Community Contributo)
  • When: 1 May 2013 1:22 AM
  • Last revision by (eMicrosoft Partne)
  • When: 9 Sep 2013 10:41 AM
  • Revisions: 7
  • Comments: 6
Options
RSSSubscribe to Article (RSS)
Can You Improve This Article?
Positively! Click Sign In to add the tip, solution, correction or comment that will help other users.

Report inappropriate content using these instructions.
Wiki  >  TechNet Articles  >  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

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.
, , , , , , [Edit tags]
Leave a Comment
  • Please add 4 and 7 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Posted by
    on 2 May 2013 6:21 AM

    good job

  • Posted by
    on 12 Jul 2013 11:02 PM

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

  • Posted by
    on 9 Sep 2013 9:24 AM

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

  • Posted by
    on 10 Sep 2013 7:48 PM

    Hi Naomi ,

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

  • Posted by
    on 10 Sep 2013 7:50 PM

    Hi Naomi ,

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

  • Posted by
    on 10 Sep 2013 7:56 PM

    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)