Can the SQL Case Statement 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

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 3

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.