views:

157

answers:

2

I understand how to use a 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
A: 

No, you'd need to use an IF statement for that logic since in a CASE statement you're basically returning a value. You could 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 that's about the best you could do. Personally, I'd just use an IF statement here since that's the best choice.

dcp
This is what I have been doing. It just seems like this there would be a way to use a CASE/SWITCH statement, but I guess T-SQL doesn't have one (except as I used in my first example).
Brad
+5  A: 

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

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

OMG Ponies