SQL query date null check

Question

I have the following stored procedure.

ALTER PROCEDURE [dbo].[spList_Report] 
  @id INT, 
  @startDate DATETIME = NULL, 
  @endDate DATETIME = NULL, 
  @includeStatus1 BIT, 
  @includeStatus2 BIT, 
  @includeStatus3 BIT, 
  @includeStatus4 BIT 
 
AS 
  SET NOCOUNT ON 
 
  SELECT * 
  FROM 
    tblProducts as products 
  WHERE  
    product.intID = @id 
    AND product.dateMain >= @startDate  
    AND product.dateMain <= @endDate 

If @startDate AND @endDate are both null then I want it to return the rows ignoring the date check in the where clause.

How?

Answer 1

This should do

AND product.dateMain >= ISNULL( @startDate, 0)
AND product.dateMain <= ISNULL( @endDate, product.dateMain + 1)

ISNULL yields the second value, if the first value is null.

Thus:

if @startDate is null, then dateMain must be bigger than 0 (1900-01-01)

if @endDate is null, then dateMain must be smaller than dateMain + 1 day

Answer 2

you can try something like this

ALTER PROCEDURE [dbo].[spList_Report] 
  @id INT, 
  @startDate DATETIME = NULL, 
  @endDate DATETIME = NULL, 
  @includeStatus1 BIT, 
  @includeStatus2 BIT, 
  @includeStatus3 BIT, 
  @includeStatus4 BIT 

AS 
  SET NOCOUNT ON 

  SELECT * 
  FROM 
    tblProducts as products 
  WHERE  
    product.intID = @id 
    AND product.dateMain >= ISNULL( @startDate, product.dateMain )  
    AND product.dateMain <= ISNULL( @endDate,  product.dateMain )