RAISERROR within Case statement

case, raiserror, t-sql

Solution

You can raise an error from the case expression by converting an error string to int.

select case (select count(*) from mytable)
  when 1 then 100 
  when 2 then 200
  else convert(int, 'ERROR')
end

This gives an error message like

`Conversion failed when converting the varchar value 'ERROR' to data type int.`

which is about as good as you're going to get.

Not all failed conversions give the input string in the error message. Conversions to datetime, for example, do not. So if your case expression returns a datetime, you still have to trigger the error with a string-to-integer conversion:

select case (select count(*) from mytable)
  when 1 then getdate()
  else convert(datetime, convert(int, 'ERROR'))
end

It gets worse: if you are returning a date, you can't explicitly convert that from int, so you have to resort to

convert(date, convert(char(1), convert(int, 'ERROR')))

It's pretty horrible, but in my opinion the only thing more important than clean code is informative error messages, so I live with it.

Problem

Can you not raise errors within a case statement in T-SQL? I always have problems with SQL case statements :/ ``` begin try declare @i int --set @i = (select COUNT(1) from table_name) select Item_Num = CASE (select COUNT(1) from table_name) when 1 then (select Item_Num from table_name) when 0 then (raiserror('No records in database', 0, 0)) ELSE (raiserror('Multiple records in database', 0, 0)) END from table_name end try begin catch declare @errormsg nvarchar(1024), @severity int, @errorstate int; select @errormsg = error_message(), @severity = error_severity(), @errorstate = error_state(); raiserror(@errormsg, @severity, @errorstate); end catch ```

Original source