Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

ssrs 2008 report pass parameter values to stored proc Expand / Collapse
Author
Message
Posted Friday, April 18, 2014 9:53 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 4:11 PM
Points: 366, Visits: 428
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?
Post #1563089
Posted Friday, April 18, 2014 1:14 PM This worked for the OP Answer marked as solution
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 6:38 AM
Points: 33, Visits: 65
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
Post #1563149
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse