SQL Server using IIF in Where Clause Syntax Error

Question

I'm using SQL Server 2014. I keep getting a red squiggly lines under the last = sign and last parenthesis:

DECLARE @I_sYear char(4); 
SET @I_sYear = '2021'

SELECT
    PriceType,
    SUM(PriceAmount) AS PriceAmount,
    DATEPART(YEAR, PostedDate) AS year,
    DATEPART(MONTH, PostedDate) AS month
FROM 
    Stage
WHERE
    IIF(ISNULL(@I_sYear, '') = '', 1, DATEPART(YEAR, PostedDate) = @I_sYear)

But if I don't use the IIF and just use Where DATEPART(YEAR, PostedDate) = @I_sYear it works fine.

Basically I'm just trying to evaluate if a year was passed in and if so use that year, if not select all rows.

What is the reason for that syntax error?

Answer 1

The problem is the final argument to IIF():

DATEPART(YEAR, PostedDate) = @I_sYear

This has two issues. First, the DATEPART() result is an int, but the code tries to compare it with a varchar. You have to cast one side or the other so the types match.

Second, it's trying to represent a boolean result as 1 or 0, to match with the 1 from the prior IIF() argument. SQL Server will not do that.

You need a CASE expression to get the 1 or 0 result, but again: for SQL, that's not the same as boolean. Thankfully, we can simplify this even further:

WHERE DATEPART(YEAR, PostedDate) = COALESCE(Cast(@I_sYear As int), DATEPART(YEAR, PostedDate))

No IIF() or CASE involved at all, and this could be even simpler, faster, and safer if you're open to declaring @I_sYear as an int in the first place:

WHERE DATEPART(YEAR, PostedDate) = COALESCE(@I_sYear, DATEPART(YEAR, PostedDate))