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!