Error Handling in SQL Server

Exception is a runtime error which prevents the normal execution of code.There are different  ways of error handling in SQL Server.

Each error in SQL Server has certain information attached to it.Some of the important information attached to each error is:

• Message Number     Each error has a error number.
• Severity  Number     Each error has a associated severity number.This number identifies the severity of the error.For example severity numbers from 1 to 10 do not specify an error.So messages in this range could be informational or warning.Severity numbers from 11-16 specifies programming errors.
• Error Text   The text of the error.

Errors which SQL Server can generate are stored in the sys.messages catalog view.So if we want to see the error details we can use the following query:

select * from sys.messages

This will display the list of errors defined by sql server as:

 Returning Error Information using RAISERROR

We can raise the errors ourselves depending on the application logic.Raiserror is a system function which is used to raise and send the errors to the calling procedure or the application.The application which calls the procedure should be informed about the success or failure of the procedure.
If the calling application is informed about the error in the execution of the procedure or the query then it can take appropriate action to fix the error.

We can pass the message and other error details to the RAISERROR function which is returned to the calling application.

Following is the syntax to call the RAISERROR() function.

RAISERROR ('Error Message', Severity, State);

The following will return a message with severity 10.

RAISERROR('This is just a message',10,1);

If we execute the above statement in Query Editor we get the following output.

Some useful severity numbers are:

• 10      No Error occurred.Used for returning just a message
• 15      Warning
• 16      Error Occurred.Statement could not execute

State is a integer from 0 to 255.It is used to specify different values so the source of the error can be identified.

Error handling using @@error

@@error is a global variable which contains the error number of the last executed statement.It is updated after the execution of each statement.

If the last command executed successfully then @@error will contain the value 0.If we execute the following commands

PRINT 'HELLO FROM SQL SERVER'
PRINT @@error

then 0 is printed as the value of @@error since the first PRINT statement successfully executes.

It is important to understand that the value of @error becomes 0 if the most recent statement executed successfully.So it is useful to store the error number in another variable if we want to retrieve the error number later on.

Error handling using TRY CATCH

Using @@error was the only way of implementing error handling in the previous versions of SQL Server.SQL Server 2005 introduced the TRY CATCH statements for error handling.In SQL Server 2005 and later versions TRY CATCH is a better option of handling errors.

TRY CATCH is implemented in SQL Server as:

BEGIN TRY
T-SQL STATEMENT BLOCK
END TRY
BEGIN CATCH
T-SQL STATEMENTS BLOCK TO HANDLE THE ERROR
END CATCH;
T-SQL STATEMENTS

So the statements marked on the line number 2,5 and 7 above will execute when

• Statement on the line number 2 is the statement which we want to monitor for the error.If it causes any error then the execution jumps to the catch block.
• Statement in the catch block e.g line number 5 is executed if there is any error in the try block e.g line number 2.
• Statements after the catch block e.g line number 7 will be executed after the catch block finishes execution.

If the try block calls a stored procedure and the procedure throws an error then the control is transferred to the catch block of the calling statement.

Example of TRY .. CATCH

The UserInfo table contains the user information.

CREATE TABLE [dbo].[UserInfo](
[UserName] [varchar](50) NULL,
[FIRSTNAME] [varchar](50) NULL,
[LASTNAME] [varchar](50) NULL,
[COUNTRY] [varchar](50) NULL,
[ID] [int] NULL
) ON [PRIMARY]

It defines a Id column which is of type int.In the try block we are inserting a row in the  UserInfo table.But instead of int value we are passing a string value.This will cause an exception in the try block.We handle this exception in the try block

BEGIN TRY
PRINT 'INSIDE TRY BLOCK'
DECLARE @ID INT
SELECT @ID=ID FROM [dbo].[UserInfo] WHERE ID=6
SELECT CONVERT(INT,@ID)
DECLARE @INCREMENTID INT
SELECT @INCREMENTID= @ID+1
PRINT @INCREMENTID

INSERT INTO [dbo].[UserInfo]
([UserName]
,[FIRSTNAME]
,[LASTNAME]
,[COUNTRY]
,[ID])
VALUES
('harry'
,'HARRY'
,'POTTER'
,'UK'
,'A001')

PRINT 'THIS IS PRINTED IF THERE IS NO ERROR IN THE TRY BLOCK'
END TRY
BEGIN CATCH

PRINT ERROR_MESSAGE()

PRINT 'INSIDE CATCH BLOCK'
END CATCH;
PRINT 'THIS EXECUTES AFTER THE TRY BLOCK.THIS WILL EXECUTE WHETHER OR NOT ERROR OCCURS IN THE TRY BLOCK'

Useful system functions 

There some useful system functions which can be used in the TRY block to obtain detailed information about the error.Some of these functions are:

• ERROR_NUMBER()                 returns the error number.
• ERROR_SEVERITY()               returns the error severity.
• ERROR_STATE()                      returns the state number of the error.
• ERROR_PROCEDURE()         returns the name of the stored procedure or trigger where the error occurred.
• ERROR_LINE()                         returns the line number in the stored procedure or trigger which caused the error.
• ERROR_MESSAGE()               returns the Error Message string.