Error Handling in SQL Server

In this article, I am giving a quick overview about Error Handling in SQL Server. After completing this article you will understand:

How to do Error Handling in SQL Server?

How to Raise Error Manually in SQL Server?

How to ADD USER Defined Error Messages in sys.messages catalog view?

Please give your valuable suggestions and feedback to improve this article.

I have already discussed about TSQL programming basics, I will recommend you to visit the article if you are not aware of TSQL.

How to do Error Handling in SQL Server?

When you have a batch of SQL statements and in that batch of SQL Statements if any SQL statement causes error then it throws the error message but it never terminates the execution of program it continues the execution of SQL statements which comes after that SQL Statement which raised error.

Let’s understand the above problem with an example

I am creating a store procedure which divides two numbers and print the result. As you can see from the below query, I am passing two number in the procedure. I am dividing them and storing the result in variable named as @RESULT. Initially I am assigning 0 values for the @RESULT variable and later updating the variable value with the result.

CREATE DATABASE TEACHMESQLSERVER

USE TEACHMESQLSERVER

GO

CREATE PROCEDURE DIVISION_BY_ZERO(@FIRST INT, @SECOND INT)

AS

BEGIN

DECLARE @RESULT INT

SET @RESULT=0

SET @RESULT=@FIRST/@SECOND

PRINT 'THE OUTPUT IS:'+CAST(@RESULT AS VARCHAR)

END

Let’s execute the above procedure

EXEC DIVISION_BY_ZERO 20,10

 

As you can see from the result below; when we divide 20 by 10 we get 2 and our procedure prints the result as below.

Now let’s try to divide 20 by 0; its universal truth that we can’t divide any number by 0 and we will get error.

EXEC DIVISION_BY_ZERO 20,0

Here you go; we got below error message after executing the above query.

Error message clearly says that divided by zero error encountered. If you have noticed after the error message that Print Statement executed and it printed THE OUTPUT IS: 0. this is wrong right?

It should not have printed this.

To overcome this problem SQL server provides Error handling, when we do error handling the SQL statement which cause the error will be terminated and the execution of stored procedure or batch will also be stopped.

In T-SQL programming error handling is done by using TRY and CATCH construct. Errors in T-SQL code can be processed using a TRY-CATCH construct.  A TRY -CATCH construct consists of two parts TRY block and CATCH block. TRY Block contains the SQL statement which may cause error, when an error situation is detected in TYRY block, control is passed to a CATCH block where it can be processed or handled. After the CATCH block handles the exception (Error), control is then transferred to the first T-SQL statement that follows the END CATCH statement. A TRY block always starts with the BEGIN TRY statement and ends with END TRY statement. A CATCH block always starts with a BEGIN CATCH statement and ends with END CATCH statement. A CATCH block must follow a TRY block. In T-SQL each TRY block is associated with only one CATCH block.

Let’s do error handling in above procedure.

ALTER PROCEDURE DIVISION_BY_ZERO(@FIRST INT, @SECCOND INT)

AS

BEGIN

DECLARE @RESULT INT

SET @RESULT=0

BEGIN TRY

SET @RESULT=@FIRST/@SECCOND

END TRY

BEGIN CATCH

SELECT   ERROR_NUMBER() AS ErrorNumber

        ,ERROR_SEVERITY() AS ErrorSeverity

        ,ERROR_STATE() AS ErrorState

        ,ERROR_PROCEDURE() AS ErrorProcedure

        ,ERROR_LINE() AS ErrorLine

        ,ERROR_MESSAGE() AS ErrorMessage;

END CATCH

PRINT 'THE OUTPUT IS:'+CAST(@RESULT AS VARCHAR)

END

As you can see from the above code, we have kept SET @RESULT=@FIRST/@SECOND in TRY Block. It is because this line of SQL statement is creating dividing by zero error. We have CATCH block next to this TRY block. CATCH block is handling the error raised by its TRY block.

Let’s try to divide 20 by 0.

GO

EXEC DIVISION_BY_ZERO 20,0

As you can from the above output, this time we didn't get any error message but we get a message saying Divide by Zero encountered and it also terminates the execution and didn't print THE OUTPUT IS:0 like last time.

How to Raise Error Manually in SQL Server?

We can also raise errors manually at some required situations. It is used to return messages back to application using the same format as a system error or warning message is generated. For raising an error manually we use RAISERROR statement. RAISERROR can return either a user defined error message that has been created using the SP_ADDMESSAGE system stored procedure or a message string specified in the RAISERROR statement.

Syntax-  

RAISERROR (msg_id|msg_str|@local_variable, severity, State ,argument[,..n])

WITH option [,..n]

*msg_id is user defined error message number stored in the sys.messages catalog view. Using sp_addmessage system stored procedure user defined error message can be created in sys.messages table. It should be greater than 50000.When it is not specified RAISERROR raises an error message with an error number 50000.

*msg_str is a user defined message with formatting similar to the printf function in C.

*@local_variable is a variable of any valid charter data type that contains a string formatted in the same manner as msg_str. It must be char or varchar.

*Severity is the user defined severity level associated with this message. It can be 0-18.

* State is an arbitrary integer from 1-127.The value 0 and greater than 127 will raise an error.

Let’s understand this with an example

Problem - you don’t want to divide any number by 2. If anyone tries to divide with 2 it should throw error.

SQL Code-

CREATE PROCEDURE DIVISION_BY_TWO(@FIRST INT, @SECOND INT)

AS

BEGIN

Declare @RESULT int

BEGIN TRY

If @SECOND=2

RAISERROR('I AM RAISING THIS ERROR MANUALLY, SORRY YOU ARE NOT ALLOWED TO DIVIDE BY 2',15,1)

SET @RESULT=@FIRST/@SECOND

PRINT 'The Output is: '+ Cast(@RESULT as varchar(2))

END TRY

BEGIN CATCH

PRINT Error_Message()

END CATCH

END

As you can from the above query in try block, if anyone tries to divide by 2 it raise error, catch block just after the try block catch gets the control and it prints the error message written in RAISERROR under TRY block.

Let’s try to divide 20 by 2.

EXEC DIVISION_BY_TWO 20, 2

As you can see from the above result, it raised an error which was raised manually. It printed the error message written in RAISEERROR under TRY block.

If you want to customize the error message with formatting then use the RAISERROR statement as following

RAISERROR ('We don’t want to divide %d with %d',15,1,@FIRST,@SECOND)

We can also use the WITH LOG option at the end of the string to write the error message into the SQL server Log file as following.

RAISERROR (‘We don’t want to divide %d with %d’,15,1,@FIRST,@SECOND)  WITH LOG

How to ADD USER Defined Error Messages in sys.messages catalog view?

 All the predefined error list of SQL server can be found in the SYS.Messages catalog view. Query on the database with the following statement where we can view the list of predefined errors.

SELECT * FROM SYS.MESSAGES

We can add new user defined error message in SYS.Messages using the system store procedure SP_AddMessage.

Syntax:

SP_AddMessage @msgid, @severity, @msgtext, @lang, @with_log, @replace

Example:

Exec SP_AddMessage 50001,16,'I AM RAISING THIS ERROR MANUALLY, SORRY YOU ARE NOT ALLOWED TO DIVIDE BY 2'

You can confirm that above error message has been added into sys.messages table this by running below query

SELECT * FROM SYS.MESSAGES WHERE TEXT LIKE 'I AM RAISING%'

I hope you enjoyed the article don't forget to share on Facebook.

Keep Learning!