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.
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.
Code language: PHP (php)
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 CATCH
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'.
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.
Example 8.5 shows the batch that supports server-side paging (for the description of server-side paging, see “HAVING Clause, ORDER BY Clause“).
Code language: PHP (php)
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;
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.