Use comparison signs inside a sql case statement

Question

I'm looking for a way to build case statements in a sql select query using less than and greater than signs. For example, I want to select a ranking based on a variable:

DECLARE @a INT SET @a = 0  SELECT CASE           WHEN @a < 3 THEN 0          WHEN @a = 3 THEN 1          WHEN @a > 3 THEN 2        END 

I'd like to write it as:

DECLARE @a INT SET @a = 0  SELECT CASE @a          WHEN < 3 THEN 0          WHEN 3 THEN 1          WHEN > 3 THEN 2        END 

...but SQL doesn't let me use the < and > signs in this way. Is there a way that I can do this is SQL 2005, or do I need to use the code like in the first one.

The reason for only wanting the code there once is because it would make the code a lot more readable/maintainable and also because I'm not sure if SQL server will have to run the calculation for each CASE statement.

I'm looking for a VB.NET case statement equivelent:

Select Case i     Case Is < 100         p = 1     Case Is >= 100         p = 2 End Select 

Maybe it's not possible in SQL and that's ok, I just want to confirm that.

Answer 1

You can use the SIGN function as

DECLARE @a INT SET @a = 0  SELECT CASE SIGN(@a - 3)          WHEN -1 THEN 0          WHEN 0 THEN 1          WHEN 1 THEN 2        END 

If @a is smaller than 3, then @a - 3 results in a negative int, in which SIGN returns -1.

If @a is 3 or greater, then SIGN returns 0 or 1, respectively.

---

If the output you want is 0, 1 and 2, then you can simplify even more:

DECLARE @a INT SET @a = 0  SELECT SIGN(@a - 3) + 1