How can I include exceptions in my CASE WHEN Statements in SQL Server?-CodePudding

I am working on a project in SQL Server Management Studio (v18.10), and basically what I need is exception handling for some of our rules. We have functions in place to do some other calculations based on if it is an even or odd number, but because there are "exceptions" I need to be able to handle those as well. The numbers are stored as text because they need to have leading 0s in the instance that they are less than 3 characters. The code snippet below is almost exactly what I have right now.

CASE
    WHEN
        (Num % 2 = 0 
        OR Num = '001' 
        AND NOT (Num = '124')) 
        THEN /*Do something*/

    WHEN 
        (Num % 2 = 1 
        OR Num = '002' 
        AND NOT (Num = '123'))
        THEN /*Do a different thing*/

    ELSE /*Do something else*/
END

So far my exceptions in the "even" section work, it's just the odd exceptions that appear to not be working. (i.e. Num = '001' is being treated as even, but Num = '124' is ALSO still being treated as even.) It appears as though it is still reading it as even and then not going to the next WHEN statement. I have also tried using a few other variations with no success.

AND Number <> '124'
AND Number <> 124
AND NOT Number = 124

I can't figure out why these aren't working! Do I need to put the exceptions first? Do the AND exceptions need to go before the OR exceptions? I have about 10 total exceptions to deal with, and all of the exceptions in the "odd" section are not working.

CodePudding user response：

So you should really have a look at the SQL server's operator precedence chart. https://docs.microsoft.com/en-us/sql/t-sql/language-elements/operator-precedence-transact-sql?view=sql-server-ver15

There AND ranks higher over OR, so AND is evaluated first. For same rank operators they are always left to right.

lets look at even condition now.

WHEN
    (Num % 2 = 0 
    OR Num = '001' 
    AND NOT (Num = '124')) 
    THEN /*Do something*/

This really means

  (Num % 2 = 0 
    OR (Num = '001' 
    AND NOT (Num = '124')))

So if Num is 124 the first part Num%2 is true and lets it be processed.

What you really need here

(Num % 2 = 0 
        OR Num = '001') 
        AND NOT (Num = '124')

TLDR: Put parenthesis explicitly to avoid SQL to interpret incorrectly