SQL query date null

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 

I know this seems like a dumb question, but if @startDate AND @endDate are zero, I want it to return strings, ignoring the date check in the where clause.

Any help would be greatly appreciated.

Answer 1

It should do

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

ISNULL gives the second value if the first value is null.

In this way:

if @startDatenull, then dateMainmust be greater than 0 (1900-01-01)

if @endDatenull, then dateMainshould be lessdateMain + 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 ) 

Answer 3

"" Sql, :

If @startdate is null Or @enddate is null
   begin
      select without using a date range
   end
Else
   begin
      select using date range
   end

Answer 4

I would use the Kris Krause solution, but modify the IF statement to use AND. I think that if you use the first two solutions, the query mechanism can scan the table / index in the date fields. You want your queries to be as concise as possible for maximum performance, so don't run queries on unnecessary columns.

IF @startdate IS NULL AND @enddate IS NULL
BEGIN
    SELECT * FROM tblProducts as products WHERE  
    product.intID = @id 
END
ELSE
BEGIN
    SELECT * FROM tblProducts as products WHERE  
    product.intID = @id 
    AND product.dateMain >= @startDate  
    AND product.dateMain <= @endDate 
END