Geek Answers Handbook

TSQL CASE for execution control

I understand how to use the case statement to return different values:

SELECT CASE Color WHEN 'Blue' THEN 'Water' WHEN 'Black' THEN 'Oil' WHEN 'Red' THEN 'Blood' END FROM dbo.Liquid

Is there a way to use it to control flow instead of IF-ELSE, i.e.

 DECLARE @Color varchar() SELECT @Color = Color FROM dbo.Liquid WHERE ID = @MyID CASE (@Color) WHEN 'Blue' THEN SELECT 'Water' WHEN 'Black' THEN SELECT 'Oil' WHEN 'Red' THEN PRINT 'HELP! I''m bleeding!' END
+4
source share
2 answers

No, the CASE expression cannot be used to control the flow of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures.

For a list of flow control methods, see Flow Control Language (Transact-SQL) .

+8
source

No, you need to use the IF statement for this logic, since in the CASE statement you basically return a value. You can do something like this:

 declare @result varchar(500) SET @result = CASE WHEN @Color = 'Blue' THEN 'Water' WHEN @Color = 'Black' THEN 'Oil' WHEN @Color = 'Red' THEN 'HELP! I''m bleeding' END IF @Color = 'Red' PRINT @result

But I think about the best you could do. Personally, I would just use the IF here, as this is the best choice.

+1
source

More articles:


All Articles