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.

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.

ERROR_ Functions

ERROR_ functions help identify the source 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 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.

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.

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.

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.

• Our Sql INSERT Tutorial
• Our Sql UPDATE Tutorial
• Our Sql DELETE Tutorial