SQL Exception Handling

Exception Handling refers to handling query errors on SQL Server. When an error occurs, TRY CATCH exception handling offers a chance to take corrective steps or perform an alternative action.

Example

#

This example displays the error details when a divide-by-zero error occurs.

BEGIN TRY  
  -- Generate divide-by-zero error.  
  SELECT 1/0  
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

Built-in ERROR_ functions help in reporting the exception details.

Result:  1 record
ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorLine ErrorMessage
8134 16 1 NULL 3 Divide by zero error encountered.

Using TRY CATCH

#

The TRY CATCH construct handles exceptions in SQL Server.

The TRY block contains all statements that need to be executed.

If an error or failure occurs, the code in the CATCH block will be executed.



ERROR_ Functions

ERROR_ functions help identify the source of the error.

Function Description
ERROR_LINE Returns the line number where the error has occured
ERROR_MESSAGE Returns the complete description of the error
ERROR_PROCEDURE Returns the name of the stored procedure or trigger where the error occured.
If no procedure or trigger, this function returns NULL.
ERROR_NUMBER Returns the error number that occured
ERROR_SEVERITY Returns the severity level of the error occured
ERROR_STATE Returns the state number of the error

These error values are commonly logged to an Error table.
This will help developers quickly identify the source of the problem.

Syntax

Syntax of the TRY CATCH construct.

BEGIN TRY  
  -- statements that may cause errors or exceptions
END TRY 
BEGIN CATCH  
  -- statements that handle errors or exception
END CATCH  

A TRY block must always be followed by a CATCH block.


More Examples

INSERT Exceptions

CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Adding a new customer without last name.
BEGIN TRY  
  /* Customer table columns:
  FirstName and LastName NOT NULL
  City, Country, and Phone NULL */

  INSERT INTO Customer VALUES('Jessie', NULL, 'New York', 'US', '212 589 874')
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

This creates an error because LastName does not accept NULL values.

Result:  1 record
ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorLine ErrorMessage
515 16 2 NULL 6 Cannot insert the value NULL into column 'LastName', table 'dbo.Customer'; column does not allow nulls. INSERT fails.

UPDATE Exception Handling

CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Setting a customer's first name to NULL.
BEGIN TRY  
  /* Customer table columns:
  FirstName and LastName are NOT NULL
  City, Country, and Phone accept NULL */

  UPDATE Customer 
     SET FirstName = NULL
   WHERE Id = 14
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

This creates an error because FirstName does not accept NULL values.

Result:  1 record
ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorLine ErrorMessage
515 16 2 NULL 6 Cannot insert the value NULL into column 'FirstName', table 'dbo.Customer'; column does not allow nulls. UPDATE fails.

DELETE Exception Handling

CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Deleting a customer.
BEGIN TRY  
  DELETE 
    FROM Customer 
   WHERE Id = 14
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

This creates an error because the Order table has a foreign key to the Customer table. This prevents deletion. Referential integrity is maintained.

Result:  1 record
ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorLine ErrorMessage
547 16 0 NULL 2 The DELETE statement conflicted with the REFERENCE constraint "FK_ORDER_REFERENCE_CUSTOMER". The conflict occurred in table "dbo.Order", column 'CustomerId'.

You may also like



Guides


vsn 3.1