Can the SQL Case Statement fall through?

norlando picture norlando · Jan 22, 2010 · Viewed 7.5k times · Source

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.

Answer

Mark Byers picture Mark Byers · Jan 22, 2010

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.