Exception Handling in SQL Server by TRY CATCH Method

Exception Handling in SQL Server – Table of Content

Introduction to Exception handling in SQL server:

An Exception is nothing but an error mechanism that occurs in any program during the time of program execution.The methods used to resolve these errors are called Exception handling. Now Exception handling methods are widely used in the SQL Server because it’s easy to use methodology.There are several Exception handling available in SQL servers such as TRY and CATCH methods.We will be going to explain these sections later. SQL server 2005 has introduced the new Exception handling methods to help programs to run effortlessly and error-free. Sometimes it difficult to capture the errors which occurred during the time of end programming execution. Even user needs to know these kinds of mistakes happened in the end-users with programming codes.

To gain in-depth knowledge with practical experience in SQL server, then explore SQL server Training!

TRY and CATCH methods:

SQL server 2005 has introduced these two methods to handle exceptions, such as TRY and CATCH. Both these TRY and CATCH statements work similarly to any other programming language. First, this statement is executed in the SQL server statement, which the user has written in the TRY block. If the Error occurred again, then TRY will automatically get executed the CATCH block.

EXAMPLE:

BEGIN TRY # executing first the TRY statement
// now insert the SQL statements
END TRY # ending of TRY statement
Now it’s time to execute the CATCH statement
BEGIN CATCH # executing the CATCH statement
// now this statement handle the exception details
END CATCH # ending of CATCH statement
The following are the numbers of Error handling property statements in the TRY…CATCH statements;
ERROR_NUMBER () # handling the Error in NUMBER property
ERROR_STATE () # handling the Error in STATE property
ERROR_SEVERITY () # handling the SEVERITY errors
ERROR_LINE () # handling the errors in LINE property
ERROR_PROCEDURE () # handling the PROCEDURAL errors
ERROR_MESSAGE () # handling the Message errors

NOTE: Normally, SQL Server stores the Error as a message; this Error has occurred in the execution of the following statement table;
Select * from sys.messages

HANDLING EXCEPTION using TRY AND CATCH statements:

In this section, I am going to explain the steps involved in handling the Exception using TRY and CATCH statements,

Steps:

Step1: user need to create the Custom error table with the name “Error Tracer,”

Step2: user needs to write the common stored procedure for handling the current Exception in the table name Proc_InsertErrorDetails, which helps the user insert the error details into the given step1 created table already.

Step3: Now, need to write a simple procedure and execute the exception handling statement using TRY……CATCH. When you get the errors, it will automatically call the Procs_InsertErrorDeatils and inserts the Error details.

Step4: Now, it’s time to check the ErrorTracer table that will list out all the occurred error details.

Programming example:

USE [Northwind] #create the NorthWind table
GO
IF OBJECT_ID (‘databaseobject.ErrorTarcer’) IS NOT NULL value
BEGIN
DROP TABLE database object.ErrorTracer
PRINT ‘Table database object.ErrorTarcer Dropped’
END
GO

CREATE TABLE ErrorTracer
(
…………………
…………………# inserting table values
…………………..
)

IF OBJECT_ID (‘Proc_InsertErrorDetails’) IS NOT NULL

BEGIN
DROP PROCEDURE [databaseobject]. [Proc_InsertErrorDetails]
PRINT ‘procedure Proc_InsertErrorDetails Dropped’
END
GO

Syntax of Exception Handling:

TRY BLOCK
SET of SQL Statement
Throws error
> CATCH BLOCKS
Handling Exception
(Error log, Rollback)
The code is as follows;
/* SQL statements*/
END TRY #end of the try statement
BEGIN CATCH # start of the BEGIN statement
Print ERROR OR
ROLLBACK Transaction
END CATCH

In this Exception handling, SQL statements were inserted into try Blocks. If all the inserted statements were executed without any error, then the try statement ‘OK’ will go to the CATCH Block.

Types of SQL server Exceptions:

There are two types of SQL server Exceptions available; such as

System defined Exception handling

User-defined Exception handling

Let us discuss them one by one,

System defined Exception handling:

The system defined Exception handling is the system generates nothing but Exceptions.

Example:

Declare @Valu1 int;
Declare @Valu2 int;
BEGIN TRY
Set @Valu1 = 9;
Set @Valu2 = @Valu1/0; /*Error has occurred */
END TRY
BEGIN CATCH
Print ‘An error occurred:’
Print Error_Message () /* property of exception handling
END CATCH
OUTPUT:
An Error Occurred
Divided by Zero error encountered

User-Defined Exception handling:

This type of Exception is generated by the users, not by any system.

SQL Server Certification Training