Error Handling in SQL Server 2005

Error handling in previous versions of SQL Server are Tedious. we find the error occured by frequently checking the @@ERROR Variable after each statement and raise the error accordingly.
In SQL Server 2005, Try Catch Block was added to extend error handling.

-- Sample code to Try Catch
CREATE PROCEDURE someproc
AS
BEGIN
    BEGIN TRY
        SELECT * FROM authors
    END TRY
    BEGIN CATCH
       SELECT ERROR_NUMBER()
    END CATCH
END
GO

Things to be remembered while using Try Catch
1. Try Block should be follwed by a Catch Block
2. Try and Catch should be assoiciated to single batch only
3. Try and Catch Block can be nested.
4. Muliple Catch Block should not be used for a single Try Block


TRY…CATCH uses error functions to capture error information.
1. ERROR_NUMBER() returns the error number.
2. ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters such as lengths, object names, or times.
3. ERROR_SEVERITY() returns the error severity.
4. ERROR_STATE() returns the error state number.
5. ERROR_LINE() returns the line number inside the routine that caused the error.
6. ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.


Try Catch in Trasactions
The function XACT_STATE() return the state of transaction.
-1 - failed transaction
1 - success transaction

-- catch errors in a transaction
 BEGIN TRY
   SELECT * FROM authors
 END TRY
 BEGIN CATCH
   -- Test tx state
   IF (XACT_STATE()) = -1
     ROLLBACK TRANSACTION
   IF (XACT_STATE()) = 1
     COMMIT TRANSACTION
END CATCH
GO