SQL Server provides you with tools to handle errors in your T-SQL code. The main tool used for error
invoke to get information about the error. I’ll start with a basic example demonstrating the use of
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
