Try and Catch:     

 Try and Catch is great feature in sql and added in sql server 2005.

Try and Catch is same as Exception handling in OOPS.Use for Error Handling.

 

Syntax:

 

 

BEGIN

TRY

{

//The_Query_for_which_we_need_to_do_the_ Error_Handling

sql_statement | statement_block

} END
TRY

BEGIN
CATCH

//If there is some error in the query within the Try block, this flow 
  will be passed to this Catch block. 

[ { sql_statement | statement_block } ]

END
CATCH [ ; ]


In above syntax we have two block Try and Catch.

If ann error occur in Try Block then control pased to catch block.In try block ,Query will check which we need to do error handling and if there is an error then passes to catch block and error will print.

 

Example:

 

BEGIN TRY           
      Print ' I am level 0 '
          BEGIN TRY
            Print ' I am level 1 '
            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;   
    Print ' I am going out level 2   ' 
         END CATCH
    Print ' I am level 1 again '    
END TRY
BEGIN CATCH
    -- Execute the error retrieval routine.
        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

 

Error Functions

TRY…CATCH uses error functions to capture error information.

ERROR_NUMBER() returns the error number.

ERROR_MESSAGE() returns the complete text of the error message.

ERROR_SEVERITY() returns the error severity.

ERROR_STATE() returns the error state number.

ERROR_LINE() returns the line number inside the routine that caused the error.

ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.