Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can the SQL Case Statement fall through?

Is there a way to make a CASE statement in SQL fall through like the case statement in C#? What I don't want to do is the example below but if that’s my only option I guess I'll go with it.

EXAMPLE:

@NewValue =
   CASE
      WHEN @MyValue = '1' THEN CAST(@MyValue AS int)
      WHEN @MyValue = '2' THEN CAST(@MyValue AS int)
      ELSE NULL
   END

EDIT:

I'm using SQL Server.

like image 693
norlando Avatar asked Jan 22 '10 20:01

norlando


People also ask

Does SQL case evaluate all conditions?

This is standard SQL behaviour: A CASE expression evaluates to the first true condition. If there is no true condition, it evaluates to the ELSE part. If there is no true condition and no ELSE part, it evaluates to NULL .

Is CASE statement supported in SQL?

The case statement in SQL returns a value on a specified condition. We can use a Case statement in select queries along with Where, Order By, and Group By clause. It can be used in the Insert statement as well.

How does CASE statement works in SQL?

The SQL CASE Statement The CASE statement goes through conditions and returns a value when the first condition is met (like an if-then-else statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.

HOW DO CASE statements work?

The CASE statement chooses from a sequence of conditions, and executes a corresponding statement. The CASE statement evaluates a single expression and compares it against several potential values, or evaluates multiple Boolean expressions and chooses the first one that is TRUE .


1 Answers

To answer your specific question: No, it cannot.

See, for example, the MySQL documentation for CASE. Every WHEN must have a THEN result, and there is no way around this. The THEN is not marked as optional. The same applies to all other RDBMS I've used.

Here's another example: Sql Server's CASE expression

You already have a good alternative way to do it posted as a comment, so I won't repeat it here.

like image 106
Mark Byers Avatar answered Sep 20 '22 11:09

Mark Byers