SSRS Reporting Service - Multiple Parameters

Having come across this problem time and time again I thought I would share this useful little trick to passing in multiple parameters with SSRS.

First of all, create a function that will parse your multiple values to make it seem like you are passing an array...take a look at this:

----------------------------------------------------------
CREATE FUNCTION dbo.ufn_Split
(
@String nvarchar(4000),
@Delimiter char(1)
)

RETURNS @Results TABLE (value nvarchar(4000))
AS

BEGIN
DECLARE @INDEX INT
DECLARE @SLICE nvarchar(4000)

SELECT @INDEX = 1

IF @String IS NULL RETURN
WHILE @INDEX !=0
BEGIN
SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
IF @INDEX !=0
SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
ELSE
SELECT @SLICE = @STRING
INSERT INTO @Results(value) VALUES(@SLICE)
SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
IF LEN(@STRING) = 0 BREAK
END

RETURN
END
----------------------------------------------------------

And then to use the function here is an example

----------------------------------------------------------
CREATE PROCEDURE [dbo].[rpt_GetPackingSlip]
(
@OrderID varchar(100)
)
AS
SELECT
c.customer_ID,
c.title_code,
c.first_name,
c.surname,
c.email,
o.order_ID,
oi.price_paid,
oi.product_attributes,
oi.product_name as ProductName,
oi.quantity
FROM
Customer c
INNER JOIN [Orders] o on o.customer_ID = c.customer_ID
LEFT JOIN Order_Item oi on oi.order_ID = o.order_ID
WHERE
o.order_ID IN (SELECT value FROM dbo.ufn_Split(@OrderID, ','))
----------------------------------------------------------

I hope this helps!