May 7, 2014 at 4:27 pm
In an SSRS 2008 r2 report, I am currently calling a stored procedure called spRoom. I obtain the results of the stored procedure
by creating a temptable called #roomReults
The temp table that I am creating and using to obtain results looks like the following:
CREATE TABLE #roomResults(
studentID VARCHAR(15),
endYear SMALLINT,
calendarID INT)
INSERT #roomResults
EXEC [dbo].[spRoom] @endYear, @calendarID
Currently I am only passing in one value for both paramters called: @endYear and @calendarID. However now I want to pass
in several values to each parameter.
I want to change the sql to look like the following:
EXEC [dbo].[spRoom] IN (@endYear), In (@calendarID)
The above gives me syntax errors.
Thus I am wondering if you can show me how to change the sql listed above so that that I can pass in more than one value from the SSRS report parameters called @endYear and @calendarID to the stored procedure called [spRoom]?
May 8, 2014 at 2:45 am
If the report has multi valued parameters (and there are not too many of them) then you can pass the comma separated values to the procedure and use Jeff Moden's splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/
Far away is close at hand in the images of elsewhere.
Anon.
May 8, 2014 at 12:11 pm
David's suggestion is how I have always done it. In your WHERE statement you then have something like
WHERE endYear IN (SELECT Param FROM bk_report.dbo.fn_StringSplitter(@endYear, ','))
Then in SSRS make sure the @endYear parameter has the multi-value box checked.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply