SSRS Report Parameter handling in SQL Query

Question

I have a SSRS report where I pass start date and end date to filter my data. I need to get those records having data between passed date values.I have query as below..

SELECT * FROM Sample s WHERE s.RecStartDate >= ISNULL(@StartDate,s.RecStartDate)
AND s.RecEndDate <= ISNULL(@EndDate,s.RecEndDate)

Here I have to handle 'NULL' and data is from two different fields. But the problem is, in my SSRS report default value for those two dates is 'TODAY'. When I choose @StartDate as 'TODAY' and @EndDate as 10/12/2010, it will never work as per above condition. Please help me solve this.

Thanks, Su.

I am not getting any error. But its not the intended behaviour. Is it? I have to restrict the user to not to enter EndDate less than StartDate. They can be equal though. Thats my requirement. @Roshan — SR1991

M.Ali M.Ali · Accepted Answer · 2014-04-21T14:16:31

Create the following procedure and use this stored procedure in your report's dataset.

CREATE PROCEDURE dbo.SomeReportProc
@StartDate DATETIME,
@EndDate   DATETIME
AS
BEGIN
  SET NOCOUNT ON;

   DECLARE @Sql NVARCHAR(MAX);

 SET @Sql = N'SELECT * FROM Sample s WHERE 1 = 1 '
            + CASE WHEN @StartDate IS NOT NULL 
                  THEN N' AND s.RecStartDate >= @StartDate ' ELSE N'' END
            + CASE WHEN @EndDate IS NOT NULL 
                  THEN N' AND s.RecEndDate <= @EndDate '     ELSE N'' END

   EXECUTE sp_executesql @Sql
                        ,N'@StartDate DATETIME, @EndDate DATETIME'
                        ,@StartDate
                        ,@EndDate

END

Note

Also make sure you select the parameter type to be datetime so when the users enter the date they use that nice GUI for selecting dates rather than inserting dates in different formats .

Datetime Parameter ON report

enter image description here

Datetime Parameter Properties

enter image description here

SSRS Report Parameter handling in SQL Query

1 Answers