Ssrs 2012 pass mutlivalues as a parameter to a stored procedure

SSAS and SSRS
1

In an SSRS 20012 report, I have an inline query that calls a stored procedure that is called spHome at my company. The call to the stored procedure is:
EXEC Test.dbo.spHome @calendarID
The call to the spHome, works if I only have 1 calendarID to pass in. However I want to pass in more that one calendarid I have a problem.
Basically when the report runs, I want the user to be able to select more than one calendarID.
Here is the sql that is in the spHome stored procedure that uses the calendarid:
CREATE TABLE #ParsedCalendars (calendarID INT)
IF @calendarID <> ''
BEGIN
INSERT INTO #ParsedCalendars
SELECT [_id]
FROM OPS.dbo.fn_splitString(@calendarID)
I know that if I pass in the calendarids like '5647,6758,9989', the stored procedure works fine.
Is there a way to take in the parameter values entered by the user and pass them in as a string to the stored procedure? The string would look like ''5647,6758,9989'
Thus can you tell me how to solve my problem?

2

are you using some sort of calendar control on the SSRS report for users to select multiple calendar dates?
How are you doing that?

3

I have a parameter called calendared and I allow the user to select multiple values.

4

I there a limit to the number of values that will be passed in?

5

what is the data type for @calendarID in the stored procedure?

1 Like
6

Options:

  1. Create a user defined table type and use it to pass in the table of CalendarIDs to join to in your procedure.
  2. Use a split string function to convert the delimited string to a table of CalendarIDs to join to in your procedure.
  3. Use a utility permanent table CREATE TABLE Cal(CalendarID int, SPID int); (Your procedure must delete it's rows when it is finished caching CalendarIDs.
1 Like