El Tianguis de Santiago Tianguistenco como antecedente del

If you always wrote bug-free code that was always accessed in a well-defined, predictable manner, you would never have any errors. However, all your code is always subject to failure. An application could attempt to pass parameters that are invalid, business rules could be

violated, or your code might not be designed to handle the calls made from a series of new applications. Therefore, you need to include error handling in your stored procedures, which allows the source of a problem to be diagnosed and fixed in a user-friendly way.

Prior to SQL Server 2005, the only way of performing error handling was to test the value of the @@ERROR global variable. When each statement is executed, SQL Server records the status of the result in @@ERROR. If an error occurred, @@ERROR contains the error number. If the statement was successful, @@ERROR contains a 0. You then need to query the variable to determine whether a statement succeeded or failed. Unfortunately, the simple act of executing a SELECT statement to retrieve the value of @@ERROR also sets the value of the variable, thereby overwriting any previous error value. Using @@ERROR to perform error handling is very cumbersome, requiring you to embed checks after each statement along with an error handling routine for each statement.

To provide a more structured way of handling errors that is very similar to the error handling routines of other programming languages, you can now use a TRY. . .CATCH block.

The TRY. . .CATCH block has two components. The TRY block is used to wrap any code in which you might receive an error that you want to trap and handle. The CATCH block is used to handle the error.

The following code creates an error due to the violation of a primary key constraint. You might expect this code to leave an empty table behind due to the error in the transaction; however, you find that the first and third INSERT statements succeed and leave two rows in the table:

--Transaction errors CREATE TABLE dbo.mytable

(ID INT NOT NULL PRIMARY KEY) BEGIN TRAN

INSERT INTO dbo.mytable VALUES(1) INSERT INTO dbo.mytable VALUES(1) INSERT INTO dbo.mytable VALUES(2) COMMIT TRAN

SELECT * FROM dbo.mytable

The reason that you have two rows inserted into the table is because by default, SQL Server does not roll back a transaction that has an error. If you want the transaction to either complete entirely or fail entirely, you can use the SET command to change the XACT_ABORT setting on your connection, as follows:

TRUNCATE TABLE dbo.mytable SET XACT_ABORT ON;

BEGIN TRAN

INSERT INTO dbo.mytable VALUES(1) INSERT INTO dbo.mytable VALUES(2) COMMIT TRAN

SET XACT_ABORT OFF; SELECT * FROM dbo.mytable

Although the SET XACT_ABORT ON statement accomplishes your goal, when you change the settings for a connection, you can have unpredictable results for an application if your code does not reset the options properly. A better solution is to use a structured error handler to trap and decide how to handle the error.

The way a TRY. . .CATCH is implemented in SQL Server 2008 is as follows:

n If an error with a severity less than 20 is encountered within the TRY block, control passes to the corresponding CATCH block.

n If an error is encountered in the CATCH block, the transaction is aborted and the error is returned to the calling application unless the CATCH block is nested within another

TRY block.

n The CATCH block must immediately follow the TRY block.

n Within the CATCH block, you can commit or roll back the current transaction unless the transaction is in an uncommitable state.

n A RAISERROR executed in the TRY block immediately passes control to the CATCH block without returning an error message to the application.

n A RAISERROR executed in the CATCH block closes the transaction and returns control to the calling application with the specified error message.

n If a RAISERROR is not executed within the CATCH block, the calling application never receives an error message.

note tRaPPinG eRRORS

A TRY. . .CATCH block does not trap errors that cause the connection to be terminated, such as a fatal error or a sysadmin executing the KILL command. You also cannot trap errors that occur due to compilation errors, syntax errors, or nonexistent objects. Therefore, you cannot use a TRY. . .CATCH block to test for an object’s existence.

The following code implements structured error handling for the previous code block:

--TRY...CATCH

TRUNCATE TABLE dbo.mytable BEGIN TRY

BEGIN TRAN

INSERT INTO dbo.mytable VALUES(1) INSERT INTO dbo.mytable VALUES(1) INSERT INTO dbo.mytable VALUES(2)

COMMIT TRAN END TRY BEGIN CATCH ROLLBACK TRAN PRINT 'Catch' END CATCH

SELECT * FROM dbo.mytable

One of the more important aspects of a TRY. . .CATCH block is that no error messages are sent to an application unless a RAISERROR is executed within the CATCH block. Within the

CATCH block, you have access to the following functions:

n ERROR_NUMBER() The error number of the error thrown n ERROR_MESSAGE() The text of the error message

n ERROR_SEVERITY() The severity level of the error message n ERROR_STATE() The state of the error

n ERROR_PROCEDURE() The function, trigger, or procedure name that was executing when the error occurred

n ERROR_LINE() The line of code within the function, trigger, or procedure that caused the error

Best PraCtiCes RetURninG SYSteM eRROR MeSSaGeS

If you implement a TRY. . .CATCH block, any errors, including system errors, are not returned to the calling application. The only way to return an error message to a calling application is to execute a RAISERROR statement. However, you can only specify a user-defined error message or dynamically construct an error message using RAISERROR. Therefore, you have a slight problem if you want to return a system-generated error to a calling application. If you want to return a system error message, you should dynamically build a message that includes the system error message information, which is returned with a RAISERROR statement that does not supply a message ID. That way, any system-generated messages are always returned with an error number of 50000.

Within the CATCH block, you can determine the current transaction nesting level with the

@@TRANCOUNT global variable. You can also retrieve the state of the innermost transaction

with the XACT_STATE function. The XACT_STATE function can return the following values: n 1 An open transaction exists that can be either committed or rolled back. n 0 There is no open transaction.

n –1 An open transaction exists, but it is in a doomed state. Due to the type of error that was raised, the transaction can only be rolled back.

XACT_ABORT behaves differently when used in conjunction with a TRY block. Instead

XACT_ABORT is turned on, any error is fatal. The transaction is left in a doomed state and XACT_STATE returns –1. Therefore, you cannot commit a transaction inside a CATCH block if XACT_ABORT is turned on.

exaM tiP

Make sure you understand how TRY. . .CATCH blocks handle errors, as well as how XACT_ABORT behaves within a TRY. . .CATCH block.