SQL Server Logging inside transactions

sp_nolock_log.sql

CREATE PROCEDURE [logging].[sp_log](
    @msg	NVARCHAR(MAX) = NULL
  , @cnt	BIGINT		  = NULL
  , @proc	NVARCHAR(256) = NULL
)
AS
BEGIN         
/*
<summary>
Microsoft SQL Server NoLock logging
</summary>

<param name="msg">Logging message</param>
<param name="cnt">Additional integer information</param>
<param name="proc">Process/procedure name</param>

<example>
This example shows how to use the <code>sp_log</code> procedure.

<code>
-- First run
sp_log @proc = 'sp_start_job';

-- Regular runs
sp_log 'Message1';
sp_log 'Clean-up', 11502;
sp_log 'Well done';

-- Commit log
sp_log;
</code>

This example shows how to use the <code>sp_log</code> procedure inside transactions.

<code>
sp_log @proc = 'Batch process';

BEGIN TRY
	sp_log 'Process started';

	BEGIN TRAN;
		sp_log 'Some process';

		throw 51000, 'Exception raised';

		sp_log 'Unreachable message';
	COMMIT TRAN;
END TRY
BEGIN CATCH
	ROLLBACK TRAN;
	sp_log 'Exception raised:';
	sp_log ERROR_MESSAGE();
END CATCH;

sp_log 'Done';
</code>
</example>
*/

    SET NOCOUNT ON;
    
                 
    ---------------------------------------------------------------------------------------------------
    -- Session context names
    ---------------------------------------------------------------------------------------------------
    -- Static variables    
    DECLARE @prc_key SYSNAME = 'prc';

    -- Dynamic variables
	-- Records' counter
    DECLARE @id_key SYSNAME		= 'id';
    DECLARE @id_raw SQL_VARIANT = SESSION_CONTEXT(@id_key);
    DECLARE @id     INT			= IIF(@id_raw IS NULL, 0, CONVERT(INT, @id_raw) + 1);

	-- Fields' prefix
    DECLARE @msg_key_base SYSNAME = 'msg_'; 
    DECLARE @cnt_key_base SYSNAME = 'cnt_';
    DECLARE @ts_key_base  SYSNAME = 'ts_';                                         

	-- Contatenated prefix and counter
    DECLARE @msg_key SYSNAME      = CONCAT(@msg_key_base, @id); 
    DECLARE @cnt_key SYSNAME      = CONCAT(@cnt_key_base, @id);
    DECLARE @ts_key  SYSNAME      = CONCAT(@ts_key_base,  @id);
    DECLARE @ts      DATETIME2(3) = GETDATE();


    ---------------------------------------------------------------------------------------------------
    -- Collect or commit?
    ---------------------------------------------------------------------------------------------------   
    EXEC sp_set_session_context @id_key, @id; 

    -- Initial record. Begin collecting    
    IF @id = 0 AND @msg IS NULL BEGIN       
        EXEC sp_set_session_context @ts_key,  @ts;  
        EXEC sp_set_session_context @prc_key, @proc;  
        EXEC sp_set_session_context @msg_key, 'Start proc';     
        EXEC sp_set_session_context @cnt_key, @cnt;   
    END;
    
    -- Regular record. Continue collecting
    IF @id > 0 AND @msg IS NOT NULL BEGIN        
        exec sp_set_session_context @ts_key,  @ts;    
        exec sp_set_session_context @msg_key, @msg;     
        exec sp_set_session_context @cnt_key, @cnt;     
    END;

    -- Final record. Commit log
    IF @id > 0 AND @msg IS NULL BEGIN
        DECLARE @i INT = 0;
        WHILE @i < @id BEGIN
			-- Extract fields by id
            SET @ts_key  = CONCAT(@ts_key_base,  @i);
            SET @msg_key = CONCAT(@msg_key_base, @i); 
            SET @cnt_key = CONCAT(@cnt_key_base, @i);

            INSERT [logging].[log] (
                [loadDtm]
              , [proc]
              , [msg]
              , [cnt]
            )    
                VALUES(
					CONVERT(DATETIME2(3),  SESSION_CONTEXT(@ts_key))
                  , CONVERT(NVARCHAR(256), SESSION_CONTEXT(@prc_key))    
                  , CONVERT(NVARCHAR(MAX), SESSION_CONTEXT(@msg_key))    
                  , CONVERT(BIGINT,        SESSION_CONTEXT(@cnt_key))
				)
            ;  
            
            SET @i += 1;
        END;   

        INSERT [logging].[log] (
		    [proc]
          , [msg]
        )    
            VALUES(
                CONVERT(VARCHAR(256),  SESSION_CONTEXT(@prc_key))          
              , 'End proc'                                       
			)
        ;
    END;
END;