Sql server® 2012 t-sql fundamentals

Error Handling

SQL Server provides you with tools to handle errors in your T-SQL code. The main tool used for error 

handling is a construct called TRY.

 

.

 

.CATCH. SQL Server also provides a set of functions that you can 

invoke to get information about the error. I’ll start with a basic example demonstrating the use of 

TRY.

 

.

 

.CATCH, followed by a more detailed example demonstrating the use of the error functions.

www.it-ebooks.info

---

 

CHAPTER 10

 

Programmable Objects

 

 371

You work with the TRY.

 

.

 

.CATCH construct by placing the usual T-SQL code in a TRY block (between 

the BEGIN TRY and END TRY keywords), and all the error-handling code in the adjacent CATCH block 

(between the BEGIN CATCH and END CATCH keywords). If the TRY block has no error, the CATCH 

block is simply skipped. If the TRY block has an error, control is passed to the corresponding CATCH 

block. Note that if a TRY.

 

.

 

.CATCH block captures and handles an error, as far as the caller is con-

cerned, there was no error.

Run the following code to demonstrate a case with no error in the TRY block.

BEGIN TRY 

  PRINT 10/2; 

  PRINT 'No error'; 

END TRY 

BEGIN CATCH 

  PRINT 'Error'; 

END CATCH;

All code in the TRY block completed successfully; therefore, the CATCH block was skipped. This 

code generates the following output.

5 

No error

Next, run similar code, but this time divide by zero. An error occurs.

BEGIN TRY 

  PRINT 10/0; 

  PRINT 'No error'; 

END TRY 

BEGIN CATCH 

  PRINT 'Error'; 

END CATCH;

When the divide by zero error happened in the first PRINT statement in the TRY block, control was 

passed to the corresponding CATCH block. The second PRINT statement in the TRY block was not 

executed. Therefore, this code generates the following output.

Error

Typically, error handling involves some work in the CATCH block investigating the cause of the error 

and taking a course of action. SQL Server gives you information about the error via a set of functions. 

The ERROR_NUMBER function returns an integer with the number of the error and is probably the 

most important of the error functions. The CATCH block usually includes flow code that inspects the 

error number to determine what course of action to take. The ERROR_MESSAGE function returns error 

message text. To get the list of error numbers and messages, query the sys.messages catalog view. The 

ERROR_SEVERITY and ERROR_STATE functions return the error severity and state. The ERROR_LINE 

function returns the line number where the error happened. Finally, the ERROR_PROCEDURE function 

returns the name of the procedure in which the error happened and returns NULL if the error did not 

happen within a procedure.

www.it-ebooks.info

---

372  

Microsoft SQL Server 2012 T-SQL Fundamentals

To demonstrate a more detailed error-handling example including the use of the error functions, 

first run the following code, which creates a table called dbo.Employees in the current database.

IF OBJECT_ID('dbo.Employees') IS NOT NULL DROP TABLE dbo.Employees; 

CREATE TABLE dbo.Employees 

( 

  empid   INT         NOT NULL, 

  empname VARCHAR(25) NOT NULL, 

  mgrid   INT         NULL, 

  CONSTRAINT PK_Employees PRIMARY KEY(empid), 

  CONSTRAINT CHK_Employees_empid CHECK(empid > 0), 

  CONSTRAINT FK_Employees_Employees 

    FOREIGN KEY(mgrid) REFERENCES dbo.Employees(empid) 

);

The following code inserts a new row into the Employees table in a TRY block, and if an error oc-

curs, shows how to identify the error by inspecting the ERROR_NUMBER function in the CATCH block. 

The code uses flow control to identify and handle errors you want to deal with in the CATCH block, 

and re-throws the error otherwise. 

note

  The ability to re-throw an error by using the THROW command was added in  

SQL Server 2012.

The code also prints the values of the other error functions simply to show what information is 

available to you upon error.

BEGIN TRY 

 

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

    VALUES(1, 'Emp1', NULL); 

  -- Also try with empid = 0, 'A', NULL 

 

END TRY 

BEGIN CATCH 

 

  IF ERROR_NUMBER() = 2627 

  BEGIN 

    PRINT '    Handling PK violation...'; 

  END 

  ELSE IF ERROR_NUMBER() = 547 

  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 

  ELSE 

www.it-ebooks.info

---