Raising Errors And Eyebrows With RAISERROR In SQL Server

Part One


I had a client get in touch recently because they thought they had corruption. That’s not normally my cup of tea, but I offered to jump on and take a look.

We got to talking, and they said users had been getting some nasty error messages. Stuff like this:

Msg 2745, Level 16, State 2, Procedure dbo.oh_no, Line 8 [Batch Start Line 13]
Process ID 56 has raised user error 50000, severity 20. SQL Server is terminating this process.
Msg 50000, Level 20, State 1, Procedure dbo.oh_no, Line 8 [Batch Start Line 13]
SCARY THINGS
Msg 596, Level 21, State 1, Line 13
Cannot continue the execution because the session is in the kill state.
Msg 0, Level 20, State 0, Line 13
A severe error occurred on the current command.  The results, if any, should be discarded

I don’t know if you’ve ever seen corruption error messages, but they’re… uh. Look, there’s a reason I send people to Microsoft. It’s $500 well spent.

Thankfully this was just an overzealous new developer who wanted to show people the wonders and miracles of error handling.

If you use RAISERROR with a severity level of 20 or higher, and the WITH LOG clause, it kills the connection. Without the WITH LOG CLAUSE, you won’t get a proper error, and it also wouldn’t enter a CATCH block.

CREATE OR ALTER PROCEDURE dbo.oh_no
AS
SET NOCOUNT, XACT_ABORT ON; 
BEGIN

    IF 1 < 2
        BEGIN
            RAISERROR('SCARY THINGS', 20, 1) WITH LOG, NOWAIT;
        END;

END;
GO 

EXEC dbo.oh_no;
GO

What you return to people can be mighty confusing. Especially if your application only shows the first error message. Ahem.

A SQL Server Error Message
Mercy

I’d probably be worried if I started seeing that pop up, too.

Part Two


Only somewhat related to part one, but probably a good time to mention it: If you want RAISERROR to get you to a TRY/CATCH block and throw an error, you have to use a severity 16 or higher and WITH LOG. For instance, this won’t work:

CREATE OR ALTER PROCEDURE dbo.oh_yes
AS
SET NOCOUNT, XACT_ABORT ON; 
BEGIN

    IF 1 < 2
        BEGIN TRY
            
            RAISERROR('IS THIS THING ON?', 10, 1) WITH LOG, NOWAIT;
        
            SELECT 'NO SHUT UP' AS NO_U;

        END TRY
        BEGIN CATCH
            THROW;
        END CATCH;
END;

EXEC dbo.oh_yes;

Your code will keep right on chugging along.

SQL Server Query Results
Chippy

Despite the fact that an “error” was raised.

Are you German?

You need to give this old hound a higher severity (and WITH LOG) to get it to bark.

RAISERROR('IS THIS THING ON?', 16, 1) WITH LOG, NOWAIT;

Then you get it back in red.

SQL Server Error Message
I used to be like you.

Parting


Error handling is good for all sorts of things. You can retry deadlocks, handle and log edge cases, keep loops alive, and more.

RAISERROR is useful for figuring out where you’re at in some logic — and I know, it’s deprecated — but I find using the combination of THROW and FORMATMESSAGE tedious and cumbersome. I don’t want to add things to sys.messages. I don’t want to use two functions instead of one to pretty print some information. What’s the point?

Thanks for reading!

Going Further


If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.



8 thoughts on “Raising Errors And Eyebrows With RAISERROR In SQL Server

  1. I had time where our SQL Server kiddo, did not go within the CATCH block, even for severe errors: missing tables, missing columns, errors on reaching a linked server…
    Somebod-ish talked about having a “complex” TRY block, involving a lot of decision points (IF, ELSE, second ELSE and so on…), could let our kiddo to ignore the CATCH block…
    I saw it, but, I cannot reproduce it (for other time-ish reasons), but: what do you think?
    It’s my imagination, or it could be a real thing?
    Regards
    S.

      1. I meant something like this:
        BEGIN
        BEGIN TRY

        END TRY
        BEGIN CATCH

        END CATCH
        END;

        My concern is when the BEGIN CATCH/END CATCH is not executed even if an error occurred in BEGIN TRY/END TRY.
        The first thing I can see, is that the BEGIN CATCH/END CATCH is executed but an error occurs in it… so the latest error is thrown like the BEGIN CATCH/END CATCH has never run.
        I am looking for other cases, like high severe errors in BEGIN TRY/END TRY pushing their management outside the program block.
        But for trivial tests I was not able to reproduce it.
        What do you think?

  2. Isn’t best practice to return after raising the error :-

    create procedure p as

    begin try

    insert dbo.B6_Kommun (Kommun, SvBakgr_1, SvBakgr_2, UtlBakgr_1, UtlBakgr_2, Totalt)
    VALUES (N’0125′,345,224,888,834,2343)

    end try

    begin catch
    RAISERROR(‘IS THIS THING ON?’, 10, 1) WITH LOG, NOWAIT;
    RETURN(ERROR_NUMBER())

    end catch

    go

    declare @i int
    exec @i = p

    select ‘return code is ‘,@i

    outputs

    (0 rows affected)
    IS THIS THING ON?
    |
    return code is |2627

    (1 row affected)

Comments are closed.