stored procedure with more than 1 value for each parameter RRS feed

  • Question

  • 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]?

    Wednesday, May 7, 2014 10:23 PM


  • The only way is to change the spRoom stored procedure to handle table valued parameters. Or alternatively keep the current procedure and write a new one that can handle table valued parameters and use this new procedure in the report.

    For every expert, there is an equal and opposite expert. - Becker's Law

    My blog

    My TechNet articles

    Thursday, May 8, 2014 1:42 AM

All replies