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.
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.
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'.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With