A single sql query which can handle both null or v

问题:

Using SQL Server 2008. I have a stored proc which has start and end date as input parameters for date range.

Looking for a single sql query which has a between start and end date in the where clause which can handle both cases where the dates are either both null or both have values.

I don't want to use an IF statement.

回答1:

WITH    limits AS
        (
        SELECT  COALESCE(@startDate, MIN(mydate)) AS startDate, COALESCE(@endDate, MAX(mydate)) AS endDate
        FROM    mytable
        )
SELECT  m.*
FROM    limits
JOIN    mytable m
ON      mydate BETWEEN startDate AND endDate

This will be most efficient if there is an index on mydate, since this condition is sargable and will use an Index Seek.

If there is no index, then use IFNULL constructs proposed by others.

回答2:

You can do this:

SELECT blah
FROM MyTable
WHERE 
    (@startDate IS NULL OR MyTable.StartDate >= @startDate)
    AND (@endDate IS NULL OR MyTable.EndDate <= @endDate)

But please be aware that a large number of parameters in AND clauses like this can lead to incorrectly cached query plans. There are many questions on SO about incorrect query plans and parameter 'sniffing'.

回答3:

Quassnoi's answer is probably best but here's another take:

SELECT *
FROM MyTable
WHERE 
    MyTable.StartDate >= ISNULL(@startDate, MyTable.StartDate)
    AND MyTable.EndDate <= ISNULL(@startDate, MyTable.EndDate)

回答4:

SELECT *
FROM MyTable
WHERE 
     MyTable.StartDate >= COALESCE(MyTable.StartDate, "1/1/1900") 
     /* Date selected as earliest plausible constant to avoid min() lookup */

 AND MyTable.EndDate <= COALESCE(MyTable.EndDate, "1/1/3001")
     /* Date selected as latest plausible constant to avoid max() lookup */

You need to select correct constants for your app/domain, obviously. It's a wee bit risky if you don't have constants wide enough but a lot faster than explicitly looking min/max up from the table, and most apps/domains have pretty well defined frames.

回答5:

SELECT
    Column1,....
    FROM MyTable
    WHERE MyTable.StartDate>=COALESCE(@startDate,CONVERT(datetime,'01/01/1753'))
        AND MyTable.EndDate<=COALESCE(@endDate,CONVERT(datetime,'12/31/9999'))

also, here is a very comprehensive article on this topic:

Dynamic Search Conditions in T-SQL by Erland Sommarskog

it covers all the issues and methods of trying to write queries with multiple optional search conditions

here is the table of contents:

   Introduction
      The Case Study: Searching Orders
      The Northgale Database
   Dynamic SQL
      Introduction
      Using sp_executesql
      Using the CLR
      Using EXEC()
      When Caching Is Not Really What You Want
   Static SQL
      Introduction
      x = @x OR @x IS NULL
      Using IF statements
      Umachandar's Bag of Tricks
      Using Temp Tables
      x = @x AND @x IS NOT NULL
      Handling Complex Conditions
   Hybrid Solutions – Using both Static and Dynamic SQL
      Using Views
      Using Inline Table Functions
   Conclusion
   Feedback and Acknowledgements
   Revision History

回答6:

You can do this

SELECT blah
FROM MyTable
WHERE 
1 = case 
        when @startDate IS NOT NULL then  MyTable.Date >= @startDate
    else 1 end
AND
1 = case 
        when @endDate IS NOT NULL then  MyTable.Date <= @endDate
    else 1 end

or

SELECT blah
FROM MyTable
WHERE 
(
    (@startDate is not null and @endDate is not null and MyTable.Date between @startDate and @endDate )
    or
    (@startDate is null and @endDate is null )
)

回答7:

For max value:

Case when @a > @b or @b is null then @a else @b end.

This handles nulls as well.

Simple.