SSRS adding Date Parameters causes report to spin and spin

Question

This seems so simple I can't understand where the problem is. I have done this before but not any time recently so I hope I am missing something simple.

I have a stored procedure on my 2005 SQL box that is a simple select statement with a few table joins to get the data I need.

I want to set the report to pull invoice dates using variables @StartDate and @EndDate.

When I am creating my report in SSRS 2005, I know I have to Declare and set static @StartDate and @EndDate values in the stored procedure for the report wizard to allow its creation.

Declare @StartDate datetime
Declare @EndDate datetime

Set @StartDate = '2014-08-01'
Set @EndDate = '2014-08-31'

This is all fine so far, my report gets created and I can run the report in about 3 seconds and it returns the expected 3000 rows of data for the previous month.

The problem happens after I switch the stored procedure type from command type: Text (the default type it creates the report as) to Stored Procedure and add my variables back in. (If I leave it as Text, it yells at me that it is expecting parameter @StartDate so I know I have to change it to Stored Procedure).

Then I go into the stored procedure and comment out the above 4 lines and add in (before AS BEGIN): ( @StartDate datetime, @EndDate datetime )

I then go into my Report menu and create the StartDate and EndDate parameters as DateTime.

Next, I go into my dataset properties and in the Parameters tab I add @StartDate selecting the report variable StartDate and @EndDate pointing to the EndDate variables I just created in the Report menu.

Now, the problem occurs when I view the report, I select the same dates I previously used above and run the report, but the report spins and spins and never finishes.

This is driving me crazy because I know I have done this before.

Open a Profiler trace and run your report, so you can see exactly what is being passed from SSRS to your stored proc. — Tab Alleman
It showed the date range I was expecting. But this time my report finished immediately? I hate SSRS. — user2233506

Tak Tak · Accepted Answer · 2014-09-18T17:24:01

It sounds like you are not passing the Start date and End Date to the Stored Procedure and I suspect it is retrieving all the records without condition.

In your stored procedure you need to declare @StartDate and @EndDate as parameters

CREATE PROC usp_someSP @StartDate date, @EndDate date
AS
BEGIN
    -- some code here .. select .....
END

In your report you pass the start date and end date parameters.

SSRS adding Date Parameters causes report to spin and spin

2 Answers