volume one volume one - 6 months ago 9
SQL Question

How to make a dynamic WHERE using COALESCE that has an OR clause

I have always made dynamic

WHERE
statements using
COALESCE()
to either return the value passed in or return all records.

However I can't seem to do it when I need to check against two columns, and if a parameter was not passed in then to return all records. Here is snippet of my code:

DECLARE @MaxAge INT = NULL --- Example value: 5

SELECT
*
FROM
dbo.Vehicle
WHERE
DATEPART(YEAR, RegistrationDate) <=
CASE
WHEN @MaxAge IS NOT NULL
THEN (DATEPART(YEAR,GETDATE()) - @MaxAge)
ELSE DATEPART(YEAR, RegistrationDate)
END
OR
DATEPART(YEAR, ProductionDate) <=
CASE
WHEN @MaxAge IS NOT NULL
THEN (DATEPART(YEAR,GETDATE()) - @MaxAge)
ELSE DATEPART(YEAR, ProductionDate)
END


What I'm trying to do (in plain english) is:


Give me all the rows in the vehicle table where the YEAR part of the RegistrationDate
is less than the @MaxAge or the YEAR part of the ProductionDate is less than the
@MaxAge. If @MaxAge is NULL, then give me the whole lot no matter when it was made or registered.


Sometimes the
RegistrationDate
value in the table will be NULL if a vehicle hasn't been registered yet. Sometimes the
ProductionDate
can be NULL if the seller simply doesn't know when it was made.

What would be the best way to tackle this? I've tried looking in SQL books and online, but can't really find anything to match this situation.

Answer

You can simplify this a bit:

SELECT  *
FROM    dbo.Vehicle
WHERE   @MaxAge IS NULL -- return everything if null
OR      DATEDIFF(YEAR, RegistrationDate, GETDATE()) <= @MaxAge  
OR      DATEDIFF(YEAR, ProductionDate, GETDATE()) <= @MaxAge

Note that if @MaxAge is null, both of the OR ... <= @MaxAge statements will return false. You'll still end up with everything from WHERE @MaxAge IS NULL.

I'm assuming here that @MaxAge represents the oldest registration you care about. With a MaxAge of 5, you'd return all results on or after '01/01/2011'.

Edit:

Since you said you wanted to use COALESCE(), you can do:

SELECT  *
FROM    dbo.Vehicle
WHERE   @MaxAge IS NULL -- return everything if null
OR      DATEDIFF(YEAR, COALESCE(RegistrationDate, ProductionDate), GETDATE()) <= @MaxAge

I personally think this makes it a little harder to read, but will achieve the same effect as the first query I listed.