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开发者_StackOverflow社区 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.
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.
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.
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.
精彩评论