CODEWITHSUNDEEP
sql-serverstored-procedures
wotney
wotney

Reputation: 1046

How to pass optional date parameters in SQL Server

I think this should be simple, but I keep running into problems. I simply want to return all data from a table that lies between a date range. But I want the date range to be optional.

ALTER PROCEDURE [dbo].[sp_ExistingPlacements_Get]
     @DateFrom  DATE = NULL,
     @DateTo    DATE = NULL
 AS
BEGIN
    SET NOCOUNT ON;
    SELECT *
    FROM tblExistingPlacements
    WHERE
      CreatedDT > COALESCE(NULLIF(@DateFrom, ''), @DateFrom)
      AND
      CreatedDT < COALESCE(NULLIF(@DateTo, GETDATE()), @DateTo)
END

So, if no dates are passed in, we return the entire table.

If only the start date (DateFrom) is passed, we return rows > the start date and all the up to the current date.

If only the End date (DateTo) is passed then return all the rows < the End Date

And of course if both dates are passed, return all the rows inbetween those dates.

Am I going the wrong route with COALESCE ?

Upvotes: 1

Views: 9677

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294207

Do not do this. SQL will have to create one execution plan that works in any situation. As unituitive as it sounds, is better to have three separate queries:

ALTER PROCEDURE [dbo].[sp_ExistingPlacements_Get]
     @DateFrom  DATE = NULL,
     @DateTo    DATE = NULL
 AS
BEGIN
    SET NOCOUNT ON;
    IF (@DateFrom IS NULL and @DateTo IS NULL) 
       SELECT field, field, field
       FROM tblExistingPlacements
       WHERE CreatedDT < GETUTCDATE();
    ELSE IF (@DateFrom IS NULL)
       SELECT field, field, field
       FROM tblExistingPlacements
       WHERE CreatedDT < @dateTo;
    ELSE IF (@DateTo IS NULL)
       SELECT field, field, field
       FROM tblExistingPlacements
       WHERE CreatedDT BETWEEN @DateFrom AND GETUTCDATE();
    ELSE
       SELECT field, field, field
       FROM tblExistingPlacements
       WHERE CreatedDT BETWEEN @DateFrom AND @DateTo;
END

The wisdom of returning the entire table when no parameters are specified is highly questionable, but that is not the point. Besides:

  • never use * in queries, always specify the projection list explicitly
  • always use UTC times in the database

For a thorough discussion of this topic see Dynamic Search Conditions in T-SQL.

Upvotes: 3

MarcinJuraszek
MarcinJuraszek

Reputation: 125620

Use ISNULL(@parameter) OR (--your condition--) instead of COALESCE:

BEGIN
    SET NOCOUNT ON;
    SELECT *
    FROM tblExistingPlacements
    WHERE
      ((@DateFrom IS NULL) OR CreatedDT > @DateFrom)
      AND
      ((@DateTo IS NULL) OR CreatedDT < @DateTo)
END

If parameter was not provided ISNULL return TRUE, so second part of OR won't matter.

Upvotes: 8

Related Questions