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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With