SSRS multi-value parameter using a stored procedure

Question

Group, I am working on a SSRS report that uses a stored procedure containing a few parameters. 2 of the paramters I am having problems with because I want to have the option of selecting more than one item. Here is what I have (in short).

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 retun 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'

--does'nt 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 could try sending the data as a single xml parameter. For details see:

http://msdn.microsoft.com/en-us/library/dd788497.aspx

Note I have not tested this together with SSRS.

Answer 2

The SQL "IN" keyword doesn't work that way.

You can't search IN this string: 'NOF, ALT' because it's a single parameter.

Answer 3

Alternatively this is a close duplicate of

http://stackoverflow.com/questions/337704/parameterizing-a-sql-in-clause

In particular David Robbins' solution looks nice.

Answer 4

You could use:

• Dynamic SQL
• Table-Valued User-Defined Function

See this link for details.