Skip to content

Error Number

2008 June 2
tags:
by Shannon Lowder

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!

One Response leave one →

Trackbacks and Pingbacks

  1. SQL201-The Syllabus | Shannon Lowder

Leave a Reply

Note: You can use basic XHTML in your comments. Your email address will never be published.

Subscribe to this comment feed via RSS