SQL Server using IIF in Where Clause Syntax Error

zdwk9cvp 于 2023-05-16 发布在 SQL Server

关注(0)|答案(1)|浏览(155)

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?

sql-server

来源：https://stackoverflow.com/questions/76183820/sql-server-using-iif-in-where-clause-syntax-error

1条答案

按热度按时间

rmbxnbpk1#

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))

赞(0）回复(0）举报 2023-05-16

我来回答

SQL Server using IIF in Where Clause Syntax Error

1条答案

相关问题

热门标签

最新问答