How to pass mutiple values as single parameter in ssrs

Question

I want to pass the multiple values from textbox in ssrs. i tried like as below but unable to get output.

When i pass the multiple values (IT,TL) to the below procedure it is not returning any data. Here the values passed from SSRS report parameter like IT,TL. these values reached to the backend like 'IT,TL' with single quotes. seems the single quote is causing the issue.

Any one please help me how to pass multple values as single parameter.

create procedure p2
  (
   @dname varchar(30)
  )
  as begin

  select * from emp where deptname in (@dname)

  end 

Answer 1

First, you need a splitting function (assuming you are using SQL Server). You can find these all over the internet, this is the one I use.

CREATE FUNCTION [dbo].[udf_Split] 
   (  @List      varchar(8000), 
      @Delimiter varchar(5)
   ) 
   RETURNS @TableOfValues table 
      (  RowID   smallint IDENTITY(1,1), 
         [Value] varchar(100) 
      ) 
AS 
   BEGIN

      DECLARE @LenString int 

      WHILE len( @List ) > 0 
         BEGIN 

            SELECT @LenString = 
               (CASE charindex( @Delimiter, @List ) 
                   WHEN 0 THEN len( @List ) 
                   ELSE ( charindex( @Delimiter, @List ) -1 )
                END
               ) 

            INSERT INTO @TableOfValues 
               SELECT substring( @List, 1, @LenString )

            SELECT @List = 
               (CASE ( len( @List ) - @LenString ) 
                   WHEN 0 THEN '' 
                   ELSE right( @List, len( @List ) - @LenString - 1 ) 
                END
               ) 
         END

      RETURN 

   END 

Then you can change the where clause in your stored procedure to this:

WHERE dept_name IN (SELECT value FROM dbo.udf_Split(@dname, ','))

This also assumes that the stored procedure and UDF are in the same database.