Error handling protip: @@Error resets after the next command

Leave a comment

March 30, 2020 by Kenneth Fisher

The system variable @@ERROR is a quick and easy way to retrieve the error number from the last statement run and use it in your code.

RAISERROR(18452, 16, 1, 1, N'');
PRINT @@Error;

Note: I’m using RAISERROR to cheat just a little bit rather than actually creating a real error situation.

The trick with @@ERROR is that it goes away on the very next command.

RAISERROR(18452, 16, 1, 1, N'');
IF @@ERROR > 0

This is always going to print a 0, because even though @@ERROR was greater than 0 in the condition, once the condition is over, a non-failing command has occurred and @@ERROR is set back to 0. This means you should always save the value into a variable in the very next command after the one you think might produce an error.

RAISERROR(18452, 16, 1, 1, N'');
SET @error = @@ERROR;
IF @error > 0
	PRINT @error;

Now we have a variable that contains that last error command that we can both test and use.

Of course all of that said, I’m doing this post because I see @@ERROR frequently in legacy code and it sometimes comes in handy when doing temporary debugging code but for newer permanent code you are almost always going to be better off using TRY .. CATCH. It gives you quite a few extra options, for example ERROR_NUMBER() and ERROR_MESSAGE() which return information about the error going into the CATCH block.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,755 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: