SQLTeam.com | Weblogs | Forums

Ssrs 2012 pass mutlivalues as a parameter to a stored procedure


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


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


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


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


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



  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.