RAISERROR within Case statement

Question

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

Answer 1

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.