TRY-CATCH Block in Natively Compiled Stored Procedure in SQL Server 2014

Introduction

 

Try-CATCH block is supported in the natively compiled stored procedures. As we know, the TRY - CATCH block is used for implementing error handling.

 

The following constructs are supported with the natively compiled store procedure.

• ERROR_MESSAGE() - returns the complete text of the error message
• ERROR_NUMBER() – Error number
• ERROR_LINE() - returns the error line number inside the routine
• ERROR_PROCEDURE()- returns the name of the procedure or the trigger in which the error occurred
• ERROR_SEVERITY() - returns the severity of the error
• ERROR_STATE() - returns the error state

Example
 

CREATE PROCEDURETestProc_TRY_CATCH

( )

WITH NATIVE_COMPILATION, SCHEMABINDING,

EXECUTE ASOWNER

AS

BEGIN ATOMIC WITH

(

      TRANSACTION ISOLATIONLEVEL = SNAPSHOT,

       LANGUAGE = N'English'

)

    BEGIN TRY

                   DECLARE @myInt AS INT = 'Error Test'                                               

                    END TRY

                                BEGIN CATCH

                                                SELECT

                                                       ERROR_SEVERITY() AS ErrorSeverity,

                                                       ERROR_STATE() AS ErrorState,

                                                        ERROR_PROCEDURE() AS ErrorProcedure,

                                                        ERROR_LINE() AS ErrorLine,

                                                        ERROR_MESSAGE() AS ErrorMessage

                                      END CATCH        

END