Post reply

ssrs 2012 pass parameter with multiple values to a stored procedure

  • June 20, 2018 at 9:46 am

    #363206

    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?

  • June 20, 2018 at 11:43 am

    #1994691

    Use the JOIN function in SSRS and you can get a comma delimited list.   Just be sure to change your dataset to use a text query that adds the JOIN function to the query string in the right place.   You'll need an EXEC for your stored procedure name as the beginning of that query string.   Don't just type this in to the query text box, you'll want to use the Expression ellipsis.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply