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

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