ssrs 2012 pass parameter with multiple values to a stored procedure

• wendy elizabeth

  SSCoach

  Points: 16915

  More actions

  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?

• sgmunson

  SSC Guru

  Points: 110651

  More actions

  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)