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:
|