T-SQL Error Handling

T-SQL developers often used the combination of the @@ERROR global variable and the RETURN or GOTO statement inside a batch or a stored procedure to handle errors. Because SQL Server clears or resets @@ERROR after every statement, in order to catch the error, it is required to check for @@ERROR immediately after the statement. SQL Server did not provide any other useful information besides @@ERROR on what went wrong.

SQL Server 2005 adopts the modern error-handling paradigm and introduces support for trY...CATCH blocks. You can place multiple statements in a trY block and catch any error in the CATCH block. Within the CATCH block, you can invoke functions such as ERROR_MESSAGE(), ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE(), and ERROR_PROCEDURE() to get more information on the error. Here's an example:

USE AdventureWorks GO IF OBJECT_ID('Production.tblTry_Catch_Audit') IS NOT NULL    DROP TABLE Production.tblTry_Catch_Audit; GO CREATE TABLE Production.tblTry_Catch_Audit ( ErrCode INT, ErrMessage NVARCHAR(4000),   ErrUser NVARCHAR(100) DEFAULT SYSTEM_USER NOT NULL,   ErrDate DATETIME DEFAULT GETDATE() NOT NULL); GO IF OBJECT_ID('Production.spTRY_CATCH') IS NOT NULL    DROP PROCEDURE Production.spTRY_CATCH GO CREATE PROCEDURE Production.spTRY_CATCH (@ProductID INT) AS BEGIN    SET NOCOUNT ON    DECLARE @errorString  NVARCHAR(4000)    DECLARE @rowcount     INTEGER    BEGIN TRY       DELETE FROM Production.Product         WHERE ProductID = @ProductID;    END TRY    BEGIN CATCH       SELECT @errorString = 'ERROR '              + CONVERT(NVARCHAR(100), ERROR_NUMBER())              + ' in procedure ''' + ERROR_PROCEDURE()              + ''' while deleting the product.'              + CHAR(13) + ERROR_MESSAGE();       INSERT INTO tblTry_Catch_Audit (ErrCode, ErrMessage)          VALUES(ERROR_NUMBER(), @errorString);       RAISERROR(@errorString, 10, 1);       RETURN ERROR_NUMBER();    END CATCH    RETURN 0 END; GO EXEC Production.spTRY_CATCH @ProductID = 1; GO SELECT * FROM Production.tblTry_Catch_Audit; GO

In this example, deleting a product creates a referential integrity error, which is captured by the trY...CATCH block. The CATCH block generates a more descriptive error string, saves that into an error audit table, and returns that, along with the actual error message and error number, to the caller.

You should keep in mind the following when writing trY...CATCH blocks:

  • The END TRY statement in a TRY...CATCH block must be immediately followed by a BEGIN CATCH statement. A syntax error is reported if you put anything between END TRY and BEGIN CATCH.

  • A TRY...CATCH construct cannot span multiple batches.

  • If the code in the CATCH block raises an error, the error is sent back to the caller, unless you have a nested trY...CATCH block in the CATCH block itself. SQL Server 2005 allows nested TRY...CATCH blocks. You can have a trY...CATCH block within another BEGIN TRY...END TRY block or BEGIN CATCH...END CATCH block.

  • The GOTO statement can be used to jump within the same trY or CATCH block or to jump out of a trY or CATCH block, but it cannot be used to enter a TRY or CATCH block.

  • trY...CATCH blocks cannot be used to capture all T-SQL errors. For example, the trY...CATCH block will not capture any syntax errors or any warning below severity level 11 or errors above severity level 20, and so on.

  • If the code in a trY block generates a trappable error, the control goes to the first line in the CATCH block. If you want to return the error information to the caller, you can use either SELECT, RAISERROR, or PRINT to do that, as illustrated in the preceding example.

Microsoft SQL Server 2005(c) Changing the Paradigm
Microsoft SQL Server 2005: Changing the Paradigm (SQL Server 2005 Public Beta Edition)
ISBN: 0672327783
EAN: 2147483647
Year: 2005
Pages: 150

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net