Sql server® 2012 t-sql fundamentals

 

CHAPTER 10

 

Programmable Objects

 

 373

  BEGIN 

    PRINT 'Re-throwing error...'; 

    THROW; -- SQL Server 2012 only 

  END 

 

  PRINT '    Error Number  : ' + CAST(ERROR_NUMBER() AS VARCHAR(10)); 

  PRINT '    Error Message : ' + ERROR_MESSAGE(); 

  PRINT '    Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10)); 

  PRINT '    Error State   : ' + CAST(ERROR_STATE() AS VARCHAR(10)); 

  PRINT '    Error Line    : ' + CAST(ERROR_LINE() AS VARCHAR(10)); 

  PRINT '    Error Proc    : ' + COALESCE(ERROR_PROCEDURE(), 'Not within proc'); 

 

END CATCH;

When you run this code for the first time, the new row is inserted into the Employees table success-

fully, and therefore the CATCH block is skipped. You get the following output.

(1 row(s) affected)

When you run the same code a second time, the INSERT statement fails, control is passed to the 

CATCH block, and a primary key violation error is identified. You get the following output.

Handling PK violation... 

Error Number  : 2627 

Error Message : Violation of PRIMARY KEY constraint 'PK_Employees'. Cannot insert duplicate key 

in object 'dbo.Employees'. 

Error Severity: 14 

Error State   : 1 

Error Line    : 3 

Error Proc    : Not within proc

To see other errors, run the code with the values 0, ‘A’, and NULL as the employee ID.

Here, for demonstration purposes, I used PRINT statements as the actions when an error was iden-

tified. Of course, error handling usually involves more than just printing a message indicating that the 

error was identified.

Note that you can create a stored procedure that encapsulates reusable error-handling code like this.

IF OBJECT_ID('dbo.ErrInsertHandler', 'P') IS NOT NULL 

  DROP PROC dbo.ErrInsertHandler; 

GO 

 

CREATE PROC dbo.ErrInsertHandler 

AS 

SET NOCOUNT ON; 

 

IF ERROR_NUMBER() = 2627 

BEGIN 

  PRINT 'Handling PK violation...'; 

END 

ELSE IF ERROR_NUMBER() = 547 

www.it-ebooks.info

---

374  

Microsoft SQL Server 2012 T-SQL Fundamentals

BEGIN 

  PRINT 'Handling CHECK/FK constraint violation...'; 

END 

ELSE IF ERROR_NUMBER() = 515 

BEGIN 

  PRINT 'Handling NULL violation...'; 

END 

ELSE IF ERROR_NUMBER() = 245 

BEGIN 

  PRINT 'Handling conversion error...'; 

END 

 

PRINT 'Error Number  : ' + CAST(ERROR_NUMBER() AS VARCHAR(10)); 

PRINT 'Error Message : ' + ERROR_MESSAGE(); 

PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10)); 

PRINT 'Error State   : ' + CAST(ERROR_STATE() AS VARCHAR(10)); 

PRINT 'Error Line    : ' + CAST(ERROR_LINE() AS VARCHAR(10)); 

PRINT 'Error Proc    : ' + COALESCE(ERROR_PROCEDURE(), 'Not within proc'); 

GO

In your CATCH block, you check whether the error number is one of those you want to deal with 

locally, in which case you simply execute the stored procedure; otherwise, you re-throw the error.

BEGIN TRY 

 

  INSERT INTO dbo.Employees(empid, empname, mgrid) 

    VALUES(1, 'Emp1', NULL); 

 

END TRY 

BEGIN CATCH 

 

  IF ERROR_NUMBER() IN (2627, 547, 515, 245) 

    EXEC dbo.ErrInsertHandler; 

  ELSE 

    THROW;  

   

END CATCH;

This way you can maintain the reusable error-handling code in one place.