Exception Handling in SQL Server

STRUCTURED EXCEPTION HANDLING:
   
    In SQL Server 2005 exception handling is carried out using Strucuted Exception, which supports to write 2 blocks.

1. TRY : It is used to monitor all those instructions in which run time errors are expected
2. CATCH : It is used to catch the thrown exceptions. This block is executed only when a exception is raised.

Syntax:

    BEGIN TRY
        statements
    END TRY
    BEGIN CATCH
        statements
    END CATCH

Note:  Following are the functions is used to display information about errors.

   
ERROR_NUMBER()      -returns the number of the error.
ERROR_STATE()      -returns the error state number.
ERROR_PROCEDURE()- returns the name of the stored procedure or trigger where the error occurred.
ERROR_LINE()      - returns the line number inside the routine that caused the error.
ERROR_MESSAGE()  - returns the complete text of the error message.

Example 1:
   
DECLARE @A INT,@B INT,@C INT
SET @A=5
SET @B=0
BEGIN TRY
   SET @C=@A/@B
   PRINT 'QUOTIENT IS.....'+CAST(@C AS VARCHAR(5))
END TRY
BEGIN CATCH
   PRINT 'CANT DIVIDE THE NUMBER BY 0'
   PRINT ERROR_NUMBER()
   PRINT ERROR_LINE()
   PRINT ERROR_MESSAGE()
   PRINT ERROR_STATE()
END CATCH
PRINT 'THANX'

EXAMPLE 2:

CREATE PROCEDURE PROC3
AS
SELECT * FROM EMP3

BEGIN TRY
     EXECUTE PROC3
END TRY

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

Thanks
GSV