passing Multiple string values in SSRS using stored procedure

Question

I'm fairly new at stored procedures and using them with SSRS.

I want to create a simple SSRS report using a Stored Procedure with a parameter allowing multiple values (results from a separate Procedure)

I have 2 simple stored procedures as Follows.

Create Procedure WO
@STARTDATE datetime, @ENDDATE datetime, @DISTRICT varchar(25)
AS
SELECT A.WO, A.CUST, A.DISTRICT, A.COMPL_DATE
FROM WORK_ORDERS A
WHERE A.COMPL_DATE between (@STARTDATE) and (@ENDDATE) 
and A.DISTRICT_NAME in (@DISTRICT)

Create Procedure DISTRICT
AS
SELECT B.DISTRICT_NAME
FROM DISTRICTS B

In my SSRS report, I'm reporting the WO Procedure using the result(s) from the DISTRICT procedure using a "DISTRICT" parameter with the available values from the DISTRICT procedure (ALLOWING MULTIPLE VALUES). Also I've used the DISTRICT Parameter in the Parameters of the WO procedure.

This works when selecting one District, but not when I select multiple districts. Anyone out there willing to help?

EricF EricF · Accepted Answer · 2013-02-28T21:24:59

If I'm not mistaken, the problem is handling the string parameter @district - presumably, SSRS is passing something that looks like "N,S,E,W", assuming 4 districts were selected - the stored proc would then come out as: ...A.District_Name in ('N,S,E,W')... which would only return a value if there were a district name 'N,S,E,W'. So, I think you need to split the incoming value and reassemble it as 'N','S','E','W'. Unfortunately, there's no SPLIT function in SQL Server, so you either have to write a UDF (there are several posts available) or make a CLR call.

Here's a clever (and untested) thought - try Replace(@District,',',''',''') (I think I got the quotes right...)

passing Multiple string values in SSRS using stored procedure

1 Answers