BEGIN TRY
    <SQL Statements>
END TRY
BEGIN CATCH TRAN_ABORT
    <SQL Statements>
END CATCH

Prior to sql server 2005 error handling is done by querying @@error after every line of execution. In sql server 2005, try catch block provides a new structure way of handling error similar to those of many development language such as VB, Java and C#

One of  the advantage of try catch block over  its old friend @@error is that it enables transaction abort errors to be captured with no loss of the transaction context

a. constraint violation

USE AdventureWorks

    BEGIN TRY
      UPDATE HumanResources.Employee
      SET Gender = 'X'
      WHERE EmployeeID = 1;
    END TRY
    BEGIN CATCH
      SELECT ERROR_NUMBER(  ) ErrorNumber,
      ERROR_STATE(  ) ErrorState,
      ERROR_SEVERITY(  ) ErrorSeverity,
      ERROR_MESSAGE(  ) ErrorMessage;
    END CATCH

 

b. selecting from non existing table

USE AdventureWorks;
GO

BEGIN TRY
    -- Table does not exist; object name resolution
    -- error not caught.
    SELECT * FROM NonexistentTable;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() as ErrorNumber,
        ERROR_MESSAGE() as ErrorMessage;
END CATCH

 

c. erroneous sp

-- Verify that the stored procedure does not exist.
IF OBJECT_ID ( N'usp_ExampleProc', N'P' ) IS NOT NULL
    DROP PROCEDURE usp_ExampleProc;
GO

-- Create a stored procedure that will cause an
-- object resolution error.
CREATE PROCEDURE usp_ExampleProc
AS
    SELECT * FROM NonexistentTable;
GO

BEGIN TRY
    EXECUTE usp_ExampleProc
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() as ErrorNumber,
        ERROR_MESSAGE() as ErrorMessage;
END CATCH;

D. division  by zero

USE AdventureWorks;
GO

BEGIN TRY
    -- Generate a divide-by-zero error.
    SELECT 1/0;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO

 

d. try catch with transaction

USE AdventureWorks;
GO
BEGIN TRANSACTION;

BEGIN TRY
    -- Generate a constraint violation error.
    DELETE FROM Production.Product
        WHERE ProductID = 980;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() as ErrorState,
        ERROR_PROCEDURE() as ErrorProcedure,
        ERROR_LINE() as ErrorLine,
        ERROR_MESSAGE() as ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

 

e. try catch with xact- state

USE AdventureWorks;
GO

-- Check to see if this stored procedure exists.
IF OBJECT_ID (N'usp_GetErrorInfo', N'P') IS NOT NULL
    DROP PROCEDURE usp_GetErrorInfo;
GO

-- Create procedure to retrieve error information.
CREATE PROCEDURE usp_GetErrorInfo
AS
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() as ErrorState,
        ERROR_LINE () as ErrorLine,
        ERROR_PROCEDURE() as ErrorProcedure,
        ERROR_MESSAGE() as ErrorMessage;
GO

-- SET XACT_ABORT ON will render the transaction uncommittable
-- when the constraint violation occurs.
SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;
        -- A foreign key constrain exists on this table. This
        -- statement will generate a constraint violation error.
        DELETE FROM Production.Product
            WHERE ProductID = 980;

    -- If the DELETE statement succeeds, commit the transaction.
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- Execute error retrieval routine.
    EXECUTE usp_GetErrorInfo;

    -- Test XACT_STATE:
        -- If 1, the transaction is committable.
        -- If -1, the transaction is uncommittable and should
        --     be rolled back.
        -- XACT_STATE = 0 means that there is no transaction and
        --     a COMMIT or ROLLBACK would generate an error.

    -- Test if the transaction is uncommittable.
    IF (XACT_STATE()) = -1
    BEGIN
        PRINT
            N'The transaction is in an uncommittable state.' +
            'Rolling back transaction.'
        ROLLBACK TRANSACTION;
    END;

    -- Test if the transaction is committable
    IF (XACT_STATE()) = 1
    BEGIN
        PRINT
            N'The transaction is committable.' +
            'Committing transaction.'
        COMMIT TRANSACTION;  
    END;
END CATCH;
GO

1.  Try catch block can be nested

2.  Only one block is allowed per batch

3.  Catches all execution errors with severity greater than 10 that do not terminate the database connection
4.  A TRY block must be followed immediately by an associated CATCH block. Placing any other statements 

      between the END TRY and BEGIN CATCH statements generates a syntax error.
5.  GOTO statements cannot be used to enter a TRY or CATCH block. GOTO statements can be used to jump to a label within the same TRY or CATCH block or to leave a TRY or CATCH block.
There is no finally block