How to Raiserror from default of CASE statement?

Question

question

user-470 asked May 10, '10

How to Raiserror from default of CASE statement?

In C#, I frequently use the defensive technique of throwing an exception if I get to the default case of a switch statement. In t-sql (specifically SQL Server 2005), there is a similar construct called CASE.

I would like to be able to use the same technique in t-sql, e.g.

SELECT (
CASE
  WHEN Blah IN ('A', 'B') THEN 3
  WHEN Blah IN ('C', 'D') THEN 2
  ELSE raiserror('unhandled blah type', 16, 1)
END) As Halb

However, t-sql gives the error:

Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'raiserror'.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ')'.

Is there any way to accomplish this type of scenario or is there another, more common, t-sql approach to this pattern?

sql-server-2005 t-sql

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-05-10T15:14:24Z

Kev Riley answered May 10, '10

CASE isn't used as control-flow in T-SQL, it is a conditional expression valuation construct.

If you really want to do this, then maybe consider setting a variable in the else condition, (in another CASE) and then check the value of the variable after the select has completed. This won't break out of the select, but at least will give you some control after it has executed.

Alternatively, validate all your values before the select, and throw the exception then.

edit: some examples to illustrate

declare @error bit

SELECT 
    (CASE
      WHEN Blah IN ('A', 'B') THEN 3
      WHEN Blah IN ('C', 'D') THEN 2
    END) As Halb
from ....

select
    @error = CASE
      WHEN Blah IN ('A', 'B') THEN @error
      WHEN Blah IN ('C', 'D') THEN @error
      ELSE 1
    END
from ...

select @error --do something here depending on the value

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg commented · May 11, 2010 at 03:28 PM

If you really need to throw that error then why not just SELECT (CASE WHEN Blah IN ('A', 'B') THEN 3 WHEN Blah IN ('C', 'D') THEN 2 ELSE 1 / 0 END) As Halb

1 ·

question