Exception Handling with TRY, CATCH, and THROW

Versions of SQL Server previous to SQL Server 2005 required error handling code after every Transact-SQL statement that might produce an error. (You can handle errors using the @@error global variable. Example 13.1 shows the use of this variable.) Starting with SQL Server 2005, you can capture and handle exceptions using two statements, TRY and CATCH. This section first explains what “exception” means and then discusses how these two statements work.

An exception is a problem (usually an error) that prevents the continuation of a program. With such a problem, you cannot continue processing because there is not enough information needed to handle the problem. For this reason, the existing problem will be relegated to another part of the program, which will handle the exception.

The role of the TRY statement is to capture the exception. (Because this process usually comprises several statements, the term “TRY block” typically is used instead of “TRY statement.”) If an exception occurs within the TRY block, the part of the system called the exception handler delivers the exception to the other part of the program, which will handle the exception. This program part is denoted by the keyword CATCH and is therefore called the CATCH block.

Note – Exception handling using the TRY and CATCH statements is the common way that modern programming languages like C# and Java treat errors.

Exception handling with the TRY and CATCH blocks gives a programmer a lot of benefits, such as:

Exceptions provide a clean way to check for errors without cluttering code
Exceptions provide a mechanism to signal errors directly rather than using some side effects
Exceptions can be seen by the programmer and checked during the compilation process

SQL Server 2012 introduces the third statement in relation to handling errors: THROW. This statement allows you to throw an exception caught in the exception handling block. Simply stated, the THROW statement is another return mechanism, which behaves similarly to the already described RAISEERROR statement.

Example 8.4 shows how exception handling with the TRY/CATCH/THROW works. It shows how you can use exception handling to insert all statements in a batch or to roll back the entire statement group if an error occurs. The example is based on the referential integrity between the department and employee tables. For this reason, you have to create both tables using the PRIMARY KEY and FOREIGN KEY constraints, as done in Example 5.11.

Example 8.4

USE sample;
BEGIN TRY
	BEGIN TRANSACTION
	insert into employee values(11111, 'Ann', 'Smith','d2');
	insert into employee values(22222, 'Matthew', 'Jones','d4'); --
referential integrity error
	insert into employee values(33333, 'John', 'Barrimore', 'd2');
	COMMIT TRANSACTION
	PRINT 'Transaction committed'
END TRY
BEGIN CATCH
	ROLLBACK
	PRINT 'Transaction rolled back';
	THROW
END CATCHCode language: PHP (php)

After the execution of the batch in Example 8.4, all three statements in the batch won’t be executed at all, and the output of this example is

Msg 547, Level 16, State 0, Line 4
The INSERT statement conflicted with the FOREIGN KEY constraint
"foreign_emp". The conflict occurred in database "sample", table
"dbo.department", column 'dept_no'.Code language: JavaScript (javascript)

The execution of Example 8.4 works as follows. The first INSERT statement is executed successfully. Then, the second statement causes the referential integrity error. Because all three statements are written inside the TRY block, the exception is “thrown” and the exception handler starts the CATCH block. CATCH rolls back all statements and prints the corresponding message. After that the THROW statement returns the execution of the batch to the caller. For this reason, the content of the employee table won’t change.

Note – The statements BEGIN TRANSACTION, COMMIT TRANSACTION, and ROLLBACK are Transact-SQL statements concerning transactions. These statements start, commit, and roll back transactions, respectively. See Chapter “Concurrency Control” for the discussion of these statements and transactions generally.

Example 8.5 shows the batch that supports server-side paging (for the description of server-side paging, see “HAVING Clause, ORDER BY Clause“).

Example 8.5

USE AdventureWorks;
DECLARE
	@PageSize	TINYINT = 20,
	@CurrentPage	INT = 4;
SELECT BusinessEntityID, JobTitle, BirthDate
	FROM HumanResources.Employee
	WHERE Gender = 'F'
ORDER BY JobTitle
OFFSET (@PageSize * (@CurrentPage - 1)) ROWS
	FETCH NEXT @PageSize ROWS ONLY;Code language: PHP (php)

The batch in Example 8.5 uses the AdventureWorks database and its Employee table to show how generic server-side paging can be implemented. The @Pagesize variable is used with the FETCH NEXT statement to specify the number of rows per page (20, in this case). The other variable, @CurrentPage, specifies which particular page should be displayed. In this example, the content of the third page will be displayed.

Related Lessons: