CODEWITHSUNDEEP
sqlsql-serverif-statementcase
Dizzy49
Dizzy49

Reputation: 1520

SQL Server : IF statement in WHERE clause

I can't seem to get the IF statement to work in my WHERE clause for SQL Server.

Basically, if @PK is 0, I want to skip those two lines in the IF statement.

Here is what I have:

WHERE
    IF (@PK <> 0)
        pk.ID >= @PK
       AND pk.ID < (@PK + 500)
    ELSE
       Set_No = Set_No
    END
    AND Set_No IN (1,2,3,4)

Also tried:

WHERE
    CASE WHEN @PK <> 0
         pk.ID >= @PK
         AND pk.ID < (@PK + 500)
       ELSE
         Set_No = Set_No
    END
    AND Set_No IN (1,2,3,4)

Edit: I searched SO and tried several things I found (including above), but they didn't work.

Upvotes: 0

Views: 82

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

I think the logic you want is more easily written using basic boolean logic operators:

WHERE (@PK = 0 OR (pk.ID >= @PK AND pk.ID < @PK + 500)) AND
      Set_No IN (1, 2, 3, 4)

Upvotes: 4

Related Questions