15

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.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Nick O
  • 3,716
  • 6
  • 38
  • 50
  • 14
    You can use `@MyValue IN ('1', '2')` to test for multiple values in a single case. – Nate C-K Jan 22 '10 at 20:07
  • 1
    DECLARE @NewValue int, @MyValue varchar(50); SET @MyValue='111'; SET @NewValue=NULL; **if ISNUMERIC (@MyValue)=1** BEGIN SET @NewValue=@MyValue END; PRINT @MyValue – KM. Jan 22 '10 at 20:35
  • Mark: Your right I do want what Nate posted but I did want to know if there was to do a fall though. – Nick O Jan 22 '10 at 20:49
  • 1
    I didn't post my response as an answer because, although I doubt SQL supports any kind of "fall through" or an imitation of it, I don't know for sure either way and I didn't feel like looking it up. I didn't post it as an answer because I wasn't really answering his question. – Nate C-K Jan 22 '10 at 21:15

3 Answers3

15

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.

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • I thought only t-sql (as in sql server and sybase) used @variable syntax? but you link in mysql? – KM. Jan 22 '10 at 20:20
  • OP should mention the database they are using, otherwise everyone here is just wasting their time guessing. picky things like this usually are DBMS specific – KM. Jan 22 '10 at 20:26
  • Added link for SQL Server too now. I'm pretty sure that my answer is not T-SQL specific, but applies to most DB. I can't say for sure if it is all - there are a lot of RDBMS, but I challenge someone to find a reasonably mainstream RDBMS that is a counter-example. ;) – Mark Byers Jan 22 '10 at 20:46
4

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.

Jose Chama
  • 2,948
  • 17
  • 22
-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.

regex
  • 3,585
  • 5
  • 31
  • 41