Logging Errors in SQL

Logging Errors in SQL :

This is the Standard way I felt to  Log SQL Errors during Batch or  Single Transactions in SP.
You can do little modifications to the below one to use it in different scenarios.

CREATE PROCEDURE [dbo].[PROCEDURE_NAME]

 --PARAMETERS EXPECTED      

AS

BEGIN

DECLARE @rtn INT = 1 //Using For Status

BEGIN TRY

BEGIN TRANSACTION

--  TRANSACTION – 1

--  TRANSACTION – 2

--  TRANSACTION – 3

--  TRANSACTION – 4

--  TRANSACTION – 5

--  TRANSACTION – 6

-

-

-

--  TRANSACTION – N

SET @rtn = 1 // If Success No ERROR then Setting as 1 to Display

COMMIT TRANSACTION

END TRY

BEGIN CATCH

       IF @@TRANCOUNT > 0 //If Transaction Any one executed then Roll Back

       BEGIN

              ROLLBACK TRANSACTION

       END

       SET @rtn = 2 //Since Failed Due To Exception then Setting as 2 to Display

       //Now Logging the Exception Detail 
       //(Note: Make ready the Table First, to Log as below)

       INSERT INTO [dbo].[ERRORLOGTABLENAME]

                     ([ERRORNO]

                     ,[ERRORDESC]

                     ,[CREATEDON])

       SELECT

               CONVERT(SYSNAME, ERROR_NUMBER())

              ,ERROR_PROCEDURE() +' : '+ CONVERT(SYSNAME, ERROR_LINE())+' : ' 
              + ERROR_MESSAGE()

              ,GETDATE()

END CATCH

SELECT  @rtn // Selecting the Status to Dispaly

END