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.
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.
Despite the fact that an “error” was raised.
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.
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.
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.
Hm. Try without catch? Sounds interesting. Give this a shot:
BEGIN
BEGIN TRY
SELECT 1 / 0;
END TRY
END;
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?
I think you’re gonna have to write your own post about this one! Sounds interesting.
I think you forgot to mention Erland Sommarskog, when you write about error handling:
This is worth your time (even it is very long): http://sommarskog.se/error_handling/Part1.html
I was forgetting about that precious resource!
Thank you very much!
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)
It’s certainly okay with me if you want to do that.