SSRS multi-value parameter using a stored procedure

Question

I am working on a SSRS report that uses a stored procedure containing a few parameters. I am having problems with two of the parameters because I want to have the option of selecting more than one item.

Here's a condensed version of what I have:

CREATE PROCEDURE [dbo].[uspMyStoredProcedure]    (@ReportProductSalesGroupID AS VARCHAR(MAX)    ,@ReportProductFamilyID AS VARCHAR(MAX)   ,@ReportStartDate AS DATETIME   ,@ReportEndDate AS DATETIME)  --THE REST OF MY QUERY HERE WHICH PULLS ALL OF THE NEEDED COLUMNS  WHERE DateInvoicedID BETWEEN @ReportStartDate AND @ReportEndDate AND ProductSalesGroupID IN (@ReportProductSalesGroupID) AND ProductFamilyID IN (@ReportProductFamilyID) 

When I try to just run the stored procedure I only return values if I enter only 1 value for @ReportProductSalesGroupID and 1 value @ReportProductFamilyID. If I try to enter two SalesGroupID and/or 2 ProductFamilyID it doesn't error, but I return nothing.

-- Returns data EXEC uspMyStoredProcedure 'G23',     'NOF',     '7/1/2009', '7/31/2009'  -- Doesn't return data EXEC uspMyStoredProcedure 'G23,G22', 'NOF,ALT', '7/1/2009', '7/31/2009' 

In SSRS I get an error that says:

Incorrect syntax near ','

It appears that the , separator is being included in the string instead of a delimiter

Answer 1

You need three things:

1. In the SSRS dataset properties, pass the multi-value param to the stored procedure as a comma-delimited string

   =Join(Parameters!TerritoryMulti.Value, ",") 

2. In Sql Server, you need a table-value function that can split a comma-delimited string back out into a mini table (eg see here). edit: Since SQL Server 2016 you can use the built-in function STRING_SPLIT for this

3. In the stored procedure, have a where clause something like this:

   WHERE sometable.TerritoryID in (select Item from dbo.ufnSplit(@TerritoryMulti,',')) 

   ... where ufnSplit is your splitting function from step 2.

(Full steps and code in my blog post 'SSRS multi-value parameters with less fail'):