Error Number

Every error generated in Microsoft SQL has a specific number, this number should uniquely identify the error that has occurred. This can be useful in building logic on what to do when a certain error is raised since creating a logic test for a number is far quicker than generating one for a string.

Let’s see how to get the error number in SQL.

To get the number of an error, call the ERROR_NUMBER() function. This function takes no argument and returns an integer. Check out the following example.

BEGIN TRY
    DECLARE @Number TINYINT,
	    @Result TINYINT
    SET @Number = 252
    SET @Result = @Number + 20
    SELECT @Number AS Number, @Result AS Result
END TRY
BEGIN CATCH
    PRINT ERROR_NUMBER()
END CATCH

This code should return 220.

When testing your code, you can keep a list of the error numbers generated. You could then build an IF..THEN statement inside your CATCH block. Then you could handle several different types of errors within one CATCH block.

BEGIN TRY
    DECLARE @Number TINYINT,
	    @Result TINYINT,
		@errorNumber INT
    SET @Number = 252
    SET @Result = @Number + 20
    SELECT @Number AS Number, @Result AS Result
END TRY
BEGIN CATCH
	@errorNumber = ERROR_NUMBER()
    IF @errorNumber = 220
        PRINT N'Something went wrong with your code'
	IF @errorNumber = 123
		PRINT N'Bad Juju man!'
	ELSE
		PRINT N'Holy Crap, what happened?'
END CATCH
GO

This is just a silly example of handling multiple errors within a single CATCH block, you of course should be more professional when implementing handlers in your own code. The one thing to note is I stored the value of ERROR_NUMBER in @errorNumber. The reason I did this is the ERROR_NUMBER will reset with the first successful T-SQL statement after the ERROR_NUMBER is raised. By storing it in @errorNumber, I can keep it until I’m through with it.

In previous versions of Microsoft SQL Server, the equivalent version of ERROR_NUMBER() was actually to call the variable @@ERROR. You can still use this function to find out what the error number in SQL 2008, but this may become deprecated in future releases, so proceed with caution.

The following is our basic error handler using @@ERROR instead of ERROR_NUMBER

BEGIN TRY
    DECLARE @Number TINYINT,
	    @Result TINYINT;
    SET @Number = 252;
    SET @Result = @Number + 20;
    SELECT @Number AS Number, @Result AS Result;
END TRY
BEGIN CATCH
    IF @@ERROR = 220
        PRINT N'Something went wrong with your code';
END CATCH
GO

If you have any questions, please send them in. I’m here to explain SQL to you. Help me, help you!

Tags:

Trackbacks/Pingbacks

  1. SQL201-The Syllabus | Shannon Lowder - 20110926

    [...] error number [...]

Leave a Reply

Notify me of followup comments via e-mail. You can also subscribe without commenting.