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

Disburden

create a flag and set its value in your CASE statement. Then call RAISERROR or the SELECT statements depending on the value of this flag... – Faline

I do not understand what you mean. – Disburden

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.

Herrah

Answer 2

Think of Case/When as operating on a single piece of data. If you think of it this way, a lot of your problems will go away.

If/Then is used to control the flow of logic.

Something like this should work for you.

declare @i int 
set @i = (select COUNT(1) from table_name)

If @i = 1
  Begin
    Print "1 row"
  End
Else If @i = 0
  Begin
    Print "no rows"
  End
Else
  Begin
    Print "too many rows"
  End

Ideality

Yeah- I use case statements in C# VB and Java- just for some reason SQL syntax throws me off... Essentially I am trying to do this logic, in SQL... switch(select count(1)...) { case 1: return (select Item_Num) case 0: throw new SqlException("No records found"); case 1: throw new SqlException("Duplicate records found"); default: throw new SqlException(); // this one would be an actual SQL exception } So I want to either rtn that scalar value (eg object o = cmd.ExecuteScalar();) then throw exceptions – Disburden

You should be able to use the code I show as a basis for your coding. Just replace the PRINT statements with the functionality you want. – Ideality

That doesn't help me learn to properly use the SQL case statement. There are several alternatives- I could handle it on the back-end. I want to know what I'm doing wrong in my SQL case statements. – Disburden

Answer 3

As I said in the comment, I think it would be easier to simply create a flag that you check outside the scope of the CASE statement. Something along the lines of:

--- code before the TRY...
BEGIN TRY
    DECLARE @i int 

    -- declare a variable to act as a flag
    DECLARE @my_flag as int

    -- than change your statement to simply fill the value of the flag 
    CASE (SELECT COUNT(1) FROM table_name)
         WHEN 1 THEN SET @my_flag = 1
         WHEN 0 THEN SET @my_flag = 0
         ELSE SET @my_flag = -1
     END

    IF (NOT @my_flag in (-1, 0))
    BEGIN
        SET @Item_Num = (SELECT Item_Num FROM table_name) -- consider a filter here
      END 
     ELSE
    BEGIN
        IF (-1 = @my_flag) RAISERROR('too many records', 0, 0)
        IF (0 = @my_flag) RAISERROR('no records', 0, 0) 
      END
END TRY
BEGIN CATCH 
    --- rest of the code goes here.... 

Faline

That looks very close to what I need, but I still get a syntax error (I was trying* to do similar when I had declared @i- I still get syntax errors and now it seems to be ignoring the declarations of the variables i and my_flag (it thinks I am trying to tag someone if I leave the "at"s in). 'Incorrect syntax near the keyword 'CASE' – Disburden

Your code won't work. You would need to change it to Set @myFlag = Case (condition) When 1 Then 1 When 0 then 0 else -1 end – Ideality

@GMastros ok thanks for the assistance- as soon as I get back from travel I will try this. I am still wondering though from original question- can you not raiserror within a case statement? From what you have posted I am assuming the answer is no, correct? Do you know- is there a reason why you can't other than "you can't" lol- like is it a matter of somehow the transaction processes or something? It just seems odd to me that you can't throw an error where you want like you can in other languages. – Disburden

@alykins You cannot throw an error where you want in other languages either. I mean... you can't throw an error in the middle of a statement because you would get a compile time error, right? Ex: x = throwError + 7 would not even compile. In other languages, you use the CASE statement to control the flow of logic. Not so in T-SQL. In T-SQL, you use If/Then to control the flow of logic. – Ideality

sure you can... switch(condition){ case 1: do something; break; case 2: throw new Exception("Invalid call to something"); case 3: do something; break; default: throw new exception("idk something");} – Disburden

Answer 4

    With #TempTable as 
    (
       Select * from ...    
       -- record set that determines if I should error out
    )  
    SELECT CASE WHEN COUNT(1) > 0 
     THEN 'Multiple records in database' 
     ELSE 0 
    END AS [Value]
    FROM #TempTable

datatype mismatch will kill the method if you're trying to error out this whole call with TSQL. Worked in my case because it was a heavy process that I needed to know was transferred correctly. If My record set was >1 then I know I should fail this. Useful if you're using SSIS or multiple methods within a .NET environment

Lushy