Can the SQL Case Statment fall through?

Question

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 1

You can also do it like this:

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

or like this:

CASE @MyValue 
         WHEN '1' THEN CAST(@MyValue AS int)
         WHEN '2' THEN CAST(@MyValue AS int)
         ELSE null
      END

even though in this case the @MyValue in ('1','2') would make more sense.

Answer 2

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.

Answer 3

Could alternatively use T-SQL try-catch. However, I'm not sure what kind of negative impact this would have on the server:

SQL:

DECLARE @intVar VARCHAR(MAX), @stringVar VARCHAR(MAX), @default_value INT, @tempVar INT
SET @default_value = NULL
SET @stringVar = 'Hello World!!'
SET @intVar = '550'

PRINT 'Casting @stringVar: '
BEGIN TRY
    SET @tempVar = CAST(@stringVar AS INT)
END TRY
BEGIN CATCH
    SET @tempVar = @default_value
END CATCH
PRINT @tempVar + 20

PRINT ''

PRINT 'Casting @intVar: '
BEGIN TRY
    SET @tempVar = CAST(@intVar AS INT)
END TRY
BEGIN CATCH
    SET @tempVar = @default_value
END CATCH
PRINT @tempVar

Output: Casting @stringVar:

Casting @intVar: 550

Furthermore, I would create user defined functions for those try catch statements that accept a varchar inputString, and int default_value, which returns the integer.

Answer 4

In short, no it can't fall through.

My instincts tell me that you might want to be looking up the COALESCE statement though. It might complement what you're doing, but without more specific details of your situation, can't be sure.