CODEWITHSUNDEEP
sql-server
Rocky
Rocky

Reputation: 19

SQL CASE checking for two conditions from the same column inside stored procedure in sql server

SQL CASE checking for two conditions from the same column stored procedure sql server not working.

SELECT *
FROM TestTable
WHERE CASE WHEN ISNULL(column1,0)=0 THEN ISNULL(OfficeFlg,0)=1 ELSE ISNULL(column2,0)=1 END

Upvotes: 0

Views: 71

Answers (2)

Thom A
Thom A

Reputation: 95689

You're trying to use a CASE expression like a CASE statement; that's not how they work.

CASE expressions return a Scalar value, not the result of a boolean expression. If I under your logic correctly, what you're after in Boolean Logic would be:

SELECT *
FROM TestTable
WHERE ((Column1 = 0 OR Column1 IS NULL)
  AND  OfficFlg = 1)
   OR (Column1 != 0 AND Column2 = 1);

Notice I've removed the use of ISNULL and the CASE. Using either in your WHERE makes the query non-SARGable, which means performance could be severely effected. For things like ISNULL instead, use IS NULL and IS NOT NULL expressions and for CASE use boolean logic with ORs and ANDs.

Upvotes: 3

Ilyes
Ilyes

Reputation: 14928

I think you are looking for

WHERE (column1 IS NULL AND OfficeFlg IS NOT NULL)
      OR
      (column1 IS NOT NULL AND column2 IS NOT NULL)

Let's see your case and try to understand from it what you need

CASE WHEN ISNULL(column1,0)=0 THEN ISNULL(OfficeFlg,0)=1 ELSE ISNULL(column2,0=1) END

  • WHEN ISNULL(column1,0)=0 THEN ISNULL(OfficeFlg,0)=1 mean column1 IS NULL AND OfficeFlg IS NOT NULL.

  • ELSE ISNULL(column2,0=1) mean column1 IS NOT NULL AND column2 IS NOT NULL.

Or maybe (cause it's not clear what are you trying to do exactly)

WHERE (column1 IS NULL AND OfficeFlg =1 )
      OR
      (column1 IS NOT NULL AND column2 =1 )

Upvotes: 3

Related Questions