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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With