Post reply

ssrs 2008 report pass parameter values to stored proc

  • April 18, 2014 at 9:53 am

    #285885

    In an SSRS 2008 r2 report, I would like to know how to pass parameter values to a stored procedure that is called within the

    main dataset of the report.

    In an SSRS 2008 r2 report, I want to add the following sql to the main dataset of the report:

    DECLARE@endYear SMALLINT = 2014,

    @singlecalendarID INT = 1,

    @PeriodSchedules CHAR(1) = N'C'

    CREATE TABLE roomResults(

    personID INT,

    firstName VARCHAR(35),

    middleName VARCHAR(30),

    lastName VARCHAR(40),

    suffix VARCHAR(50),

    fullName VARCHAR(108)

    )

    INSERT #roomResults

    EXEC [dbo].[sproom] @endYear, @calendarID, @Schedules

    When I run the sql in SSIS manager, it runs fine. However when I try to run the sql listed above,

    I am having problems getting the sql to run. I get the error message that it does not recognize the

    parameter value for @endYear.

    Thus to pass parameter values to a stored procedure [dbo].[sproom] from an SSRS report is what I believe the issue is.

    Thus can you tell me the following:

    1. For the 3 parameter values (@endYear, @calendarID, @Schedules) that the stored procedure called [dbo].[sproom] needs,

    do I set these up as the exact parameter names to the ssrs 2008 r2. If so, what kind of values do I give these parameters like the available or default values. To the main dataset that uses these parameters, do I set @endYear, @calendarID, @Schedules

    as parameters to the main dataset?

    2. If I do not setup the parameter values @endYear, @calendarID, @Schedules so the stored procedure called [dbo].[sproom] as

    parameters to the ssrs 2008 r2 report, how do I pass the parameter values to the stored procedure?

    3. The should the parameter values to the stored procedure be the exact parameter names used in the SSRS 2008 r2 report?

    Thus can you tell me and/or show me in code how to pass parameter values to a called stored procedure from an SSRS 2008 r2 report?

  • April 18, 2014 at 1:14 pm

    #1707169

    First I would create this stored procedure within your instance and not within SSRS.

    Then when you create the dataset, under Query type select stored procedure. Find your procedure name and click it. If it is not there then you do not have your data source configured correctly.

    Once you find it and select it click the Refresh Fields button. This will automatically add all of the stored proc parameters to your report.

    The answer on this MSDN question should help:

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/f7b85430-66f3-4932-a69c-3e41ddee4f3a/how-to-pass-report-parameters-to-stored-procedure-in-report-builder-30?forum=sqlreportingservices

    - Tony Sweet

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

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