SQL Server Try Catch

             SQL server provide some functionality for implementing error  handling in transaction SQL. Try catch is used for this purpose, it was introduced in 2005 version of SQL server. Try catch is basically two block, the code you want to handle error some exception would be place in try block and if there is any exception occurred in the try block would be cached in the catch block. 

           SQL server try catch block do not have any finally block. It is similar like C# programming language exception handling structure without finally block. 


            Each try block must have immediate followed by a catch block. It is possible for SQL server try catch block in nested format .

1)Below is the syntax of try catch block

BEGIN TRY   

   -- SQL Statements;
END TRY
BEGIN CATCH
        --SQL Statements;
END CATCH

2)Below is example of try catch

BEGIN TRY

         SELECT [Output]=1/0

END TRY

BEGIN CATCH

           SELECT ERROR_MESSAGE() AS [Error Message]

           ,ERROR_PROCEDURE() as [ErrorProcedure]

                      ,ERROR_LINE() AS [ErrorLine]

                     ,ERROR_NUMBER() AS [Error Number]

                     ,ERROR_SEVERITY() AS [Error Severity]

                     ,ERROR_STATE() AS [Error State]

END CATCH

Output 

Error Message ,                     ErrorProcedure,ErrorLine,Error Number ,Error Severity ,Error State
Divide by zero error encountered.    NULL              2            8134           16               1

3)Below is example of nested try catch

BEGIN TRY

         BEGIN TRY

                   SELECT [Output]=1/0

         END TRY

        BEGIN CATCH

                    SELECT  ERROR_MESSAGE() AS [Error Message]

                                 ,ERROR_PROCEDURE() as [ErrorProcedure]

                                 ,ERROR_LINE() AS [ErrorLine]

                                 ,ERROR_NUMBER() AS [Error Number]

                                 ,ERROR_SEVERITY() AS [Error Severity]

                                ,ERROR_STATE() AS [Error State]

             END CATCH

END TRY

BEGIN CATCH

--

END CATCH

4)Below is example of multiple try with single catch