We now have a finished T-SQL framework, and this is the last post of the series. We start with the complete Template (with the error engine, activity log and batching). In this post, we’ll examine each new segment and focus on the batching feature, explain what is going on and the necessity.

The finished Template:

USE DBCATCH
GO
 
IF EXISTS(SELECT 1
            FROM [dbo].[sysobjects] (NOLOCK)
           WHERE [id]                                            = OBJECT_ID(N'[dbo].[<Name, SYSNAME, PU_DB_ADD>]')
             AND OBJECTPROPERTY([id], N'IsProcedure')            = 1) 
 
BEGIN 
  DROP PROCEDURE [dbo].[<Name, SYSNAME, PU_DB_ADD>];
 
  PRINT '>>> DROPPED PROCEDURE [dbo].[<Name, SYSNAME, PU_DB_ADD>] <<<';
END 
GO
 
CREATE PROC [dbo].[<Name, SYSNAME, PU_DB_ADD>]
  @p_Batch_ID                        [UNIQUEIDENTIFIER]          = NULL
WITH ENCRYPTION
AS
 
/*************************************************************************************************/ 
/* Name        : <Name, SYSNAME, PU_DB_ADD>                                                      */
/* Version     : 1.0                                                                             */
/* Author      : Jared Kirkpatrick                                                               */
/* Date        : 2019-05-24                                                                      */
/* Description :                                                                                 */
/*************************************************************************************************/ 
/* Date        : Version: Who: Description                                                       */
/*************************************************************************************************/ 
/* 2019-05-24  : 1.0    : REB: Initial Release.                                                */
/*************************************************************************************************/ 
 
SET NOCOUNT ON;
 
/* Declare Variables */
DECLARE @v_Error                     [INT]
       ,@v_MSG                       [NVARCHAR](500)
       ,@v_DB                        [NVARCHAR](100)
       ,@v_Obj                       [NVARCHAR](100)
       ,@v_App                       [NVARCHAR](100)
       ,@v_User                      [NVARCHAR](100)
       ,@v_Spid                      [NVARCHAR](100)
       ,@v_ActivityLog_ID            [INT]
       ,@v_ADesc                     [NVARCHAR](512)
       ,@v_BatchStatus               [BIT]
       ,@v_Now                       [DATETIME]
       ,@v_LastErrorDate             [DATETIME]
       ,@v_SystemName                [NVARCHAR](100)
       ,@v_RowCount                  [INT]
       ,@v_Count                     [INT]
       ,@v_MinINT                    [INT]
       ,@v_MaxINT                    [INT]
       ,@v_Version                   [INT]       
       ,@v_KeyWord                   [SYSNAME]   
       ,@v_MSSQL_ID                  [INT]
       ,@v_Domain                    [NVARCHAR](100)  
       ,@v_Batch_Type_ID             [INT] 
	   ,@v_System                    [NVARCHAR](100)
       ,@v_SQL                       [NVARCHAR](4000)  
       ,@v_UserName                  [NVARCHAR](100)
       ,@v_PW                        [NVARCHAR](100)
       ,@v_Debug                     [INT];
 
SELECT @v_Now                        = GETDATE()
      ,@v_DB                         = DB_NAME()
      ,@v_Obj                        = '[DBCATCH].[dbo].[<Name, SYSNAME, PU_DB_ADD>]'
      ,@v_App                        = APP_NAME()
      ,@v_User                       = ISNULL(ORIGINAL_LOGIN(), USER_NAME()) 
      ,@v_Spid                       = CONVERT([NVARCHAR](25), @@SPID)
	  ,@v_Batch_Type_ID              = [dbo].[FSU_Code_GET]('Batch', 'PROCEDURE')
	  ,@v_System                     = HOST_NAME()
      ,@v_Debug                      = 0;  
 
 
/* Create Temp Tables */
 
IF @v_Debug                          = 1
BEGIN -- debug = 1 Ref: 1
 
  SELECT 'REF:1 - Define Static Variables';
 
  SELECT @v_Now                      AS [GETDATE]
        ,@v_DB                       AS [DB]
        ,@v_Obj                      AS [OBJ]
        ,@v_App                      AS [APP]
        ,@v_User                     AS [USER]
        ,@v_Spid                     AS [SPID]
        ,@p_Batch_ID                 AS [Batch_ID];
 
END; -- debug = 1    
 
/* Start the activity_log */
EXEC [DBCATCH].[dbo].[PU_ActivityLog_ADD]
  @p_Error_ID                        = NULL,
  @p_System                          = @v_System,
  @p_DB                              = @v_DB,
  @p_Obj                             = @v_Obj,
  @p_App                             = @v_App,
  @p_User                            = @v_User,
  @p_Spid                            = @v_Spid,
  @p_Description                     = NULL,
  @p_StartTime                       = @v_Now,
  @p_EndTime                         = NULL,
  @p_ActivityLog_ID                  = @v_ActivityLog_ID OUTPUT;     
 
-- Start the batch
IF @p_Batch_ID IS NULL
BEGIN  -- Create the batch
 
  SELECT @v_BatchStatus              = 1;
 
  EXEC [DBCATCH].[dbo].[PU_Batch_ADD]
    @p_Batch_ID                      = @p_Batch_ID OUTPUT
   ,@p_Type_ID                       = @v_Batch_Type_ID
   ,@p_Name                          = @v_Obj
   ,@p_Parent_ID                     = @v_ActivityLog_ID;
 
END;   -- Create the batch   
 
-- Add the next record to the batch
EXEC [DBCATCH].[dbo].[PU_BatchDetail_ADD]
  @p_Batch_ID                        = @p_Batch_ID,
  @p_Object_ID                       = @v_ActivityLog_ID,
  @p_Object                          = @v_Obj;           
 
  BEGIN TRY
 
--==>> Start Code Here!
 
 
  END TRY
 
  BEGIN CATCH
 
    SELECT @v_Error                  = 100000
          ,@v_MSG                    = CONVERT(NVARCHAR(2500), 
                                       'Error: ' + CONVERT([NVARCHAR](255), ISNULL(ERROR_NUMBER(), -1)) + 
                                       ' Severity: ' + CONVERT([NVARCHAR](255), ISNULL(ERROR_SEVERITY(), -1)) +
                                       ' State: ' + CONVERT([NVARCHAR](255), ISNULL(ERROR_STATE(), -1)) +
                                       ' Line: ' +  CONVERT([NVARCHAR](255), ISNULL(ERROR_LINE(), -1)) +
                                       ' Procedure: ' + CONVERT([NVARCHAR](255), ISNULL(ERROR_PROCEDURE(), @v_Obj)) +
                                       ' MSG: Error from [<Name, SYSNAME, PU_DB_ADD>]  with error:' + ISNULL(ERROR_MESSAGE(), ''));  
 
    EXEC [DBCATCH].[dbo].[PU_ErrorLog_ADD]
      @p_Error_ID                    = @v_Error,
      @p_System                      = @v_System,
      @p_DB                          = @v_DB,
      @p_Obj                         = @v_Obj,
      @p_App                         = @v_App,
      @p_User                        = @v_User,
      @p_ErrorMsg                    = @v_MSG;
 
    EXEC [DBCATCH].[dbo].[PU_ActivityLog_UPD]
      @p_ActivityLog_ID              = @v_ActivityLog_ID,
      @p_EndTime                     = @v_Now,
      @p_Error_ID                    = @v_Error,
      @p_Active                      = 0,
      @p_Description                 = @v_MSG,
      @p_DateModified                = @v_Now;    
 
    EXEC [DBCATCH].[dbo].[PU_BatchDetail_UPD]
      @p_ActivityLog_ID              = @v_ActivityLog_ID
     ,@p_Batch_ID                    = @p_Batch_ID
     ,@p_Now                         = @v_Now;         
 
    RETURN @v_Error;
 
  END CATCH;
 
EOF: 
 
/* Cleanup */
 
/* Update the activity Log */
EXEC [DBCATCH].[dbo].[PU_ActivityLog_UPD]
  @p_ActivityLog_ID                  = @v_ActivityLog_ID,
  @p_EndTime                         = @v_Now,
  @p_Active                          = 0,
  @p_DateModified                    = @v_Now;
 
/* Update the batch detail */
EXEC [DBCATCH].[dbo].[PU_BatchDetail_UPD]
  @p_ActivityLog_ID                  = @v_ActivityLog_ID
 ,@p_Batch_ID                        = @p_Batch_ID
 ,@p_Now                             = @v_Now;  
 
IF @v_BatchStatus                    = 1
BEGIN
 
  UPDATE [DBCATCH].[dbo].[TU_Batch]     WITH (ROWLOCK)
     SET [Active]                    = 0
	    ,[DateModified]              = GETDATE()
   WHERE [Batch_ID]                  = @p_Batch_ID;
 
END;  
GO   
 
IF OBJECT_ID('[dbo].[<Name, SYSNAME, PU_DB_ADD>]') IS NOT NULL
BEGIN
	PRINT '>>> CREATED PROCEDURE [dbo].[<Name, SYSNAME, PU_DB_ADD>] <<<';
END;
ELSE 
BEGIN
	PRINT '>>> FAILED TO CREATE PROCEDURE [dbo].[<Name, SYSNAME, PU_DB_ADD>] <<<';
END;
GO

The Input Parameter

You’ll notice first that our Template has an input parameter called @p_Batch_ID and it is set to NULL.

a finished t-sql framework

 

 

You will see soon that if the @p_Batch_ID is NULL. This means it will be the first stored procedure in the batch. It may be the only stored proc that will execute, so that batch will only have a single execution. Otherwise, the parameter will be set and passed down to the nested procedures appropriately. You will see this in action when we get to our demos.

Next, we go directly under our activity log Insert and add the logic to check the @p_Batch_ID param:

 

 

 

 

 

 

 

 

 

 

 

 

If the @p_Batch_ID was passed in and not null, this would not be the first procedure in the batch. It would skip this part of the logic if branch. But when it is the first procedure that will call other procs (we call this a wrapper procedure), we first set a variable called @v_BatchStatus to 1. This only remains in the scope of the current procedure and will not apply down the nested execution path.

When all the procedures nested have executed, the wrapper will come back to scope and we will check this variable. We’ll cover this more later when we get there. Now we will have a @p_Batch_ID and a value for @v_BatchStatus. We’ll add a record to the Batch Detail even if this is the only procedure that will execute.

 

 

 

 

 

Looking at the PU_BatchDetail_ADD proc, we see that we are passing in the @p_Batch_ID parameter that we defined in the PU_Batch_ADD call. The @v_ActivityLog_ID is stuffed into the object_id (so we need more detail about the execution of this procedure we can go directly to the activity log), and the @v_Obj variable that we defined with our other scope variables is stuffed into the Object column. This allows us to group procedures in a wrapper/process while leveraging our other features. Benefit: quick access to more detailed information in the activity logs.

This completes the initial insertion of our batching process for grouping stored procedures. Now we will continue down the template where we update the detail records as we get close to the end of the procedure execution.

our glorious t-sql framework

 

 

 

 

 

Towards the end of the TRY CATCH logic, if there was an error, a call is made to the PU_BatchDetail_UPD procedure with the activitylog_ID and the Batch_ID Param. This enables the update to use those values in a distinct where clause, where it will close the detail record with an end time, and the error will be recorded in the error log and activity log accordingly.

 

 

 

 

 

 

 

 

When the procedure executes successfully without an error, the same call is executed to the PU_BatchDetail_UPD procedure with the same parameters. We can expect corelating records in the error log and activity log will be absent an error as we would hope all executions would exit successfully.

Finally, we come to some logic that we touched on before where we evaluate the @v_BatchStatus variable:

 

 

 

 

 

When we have executed all the nested procedures, control will bubble back up to the original procedure, the wrapper. The wrapper sets the batchstatus, so when the if logic checks equates to 1, we know we are at the wrapper and we can close off the Batch. We do this by setting the active flag to 0 and updating the datemodified column to GETDATE().

The Demo Stage

That summarizes the Template of our T-SQL framework, which means we are code complete and ready to demo. Well … not quite ready to demo. We need to clean up our Error Log and Activity Log tables so they are new and ready for clean inserts.

The first table we clear is the TU_ActivityLog table by executing:

TRUNCATE TABLE TU_ActivityLog;

Next we do the same thing with the TU_Errorlog table:

TRUNCATE TABLE TU_ErrorLog;

Last we check the two tables to make sure they are clear:

 

 

 

 

Now we can create some demo procedures to test and validate our T-SQL framework. For this demo, we will create a wrapper procedure called PU_Demo1. Yes, this was the same procedure in the last two features. We will drop and then recreate the PU_Demo1 Routine so it will be more clean without having too many demo procedures lagging behind. Plus, add two nested child procedures called PU_Demo2 and PU_Demo3.

  1. PU_Demo1 – the Wrapper will print PU_Demo1 and then call procedures PU_Demo2 and PU_Demo3
  2. PU_Demo2 – the first nested procedure will print PU_Demo2 and exit back to the wrapper
  3. PU_Demo3 – the final nested procedure will print PU_Demo3, then raise an error, and then exit back to the initiating wrapper procedure.

In this case it will be easier to work backwards and develop PU_Demo3, then PU_Demo2, and last we will create PU_Demo1. Start with the code below, defining PU_Demo3. We copy our template code to a new query window and then press CTRL+SHIFT+M and type PU_Demo3 in the object line. We replace the start code here with:

SELECT 'This is PU_Demo3'
    RAISERROR ('Message', 19, 1) WITH LOG

The PU_Demo3 procedure is available in its whole below for completeness.

USE DBCATCH
GO
 
IF EXISTS(SELECT 1
            FROM [dbo].[sysobjects] (NOLOCK)
           WHERE [id]                                            = OBJECT_ID(N'[dbo].[PU_Demo3]')
             AND OBJECTPROPERTY([id], N'IsProcedure')            = 1) 
 
BEGIN 
  DROP PROCEDURE [dbo].[PU_Demo3];
 
  PRINT '>>> DROPPED PROCEDURE [dbo].[PU_Demo3] <<<';
END 
GO
 
CREATE PROC [dbo].[PU_Demo3]
  @p_Batch_ID                        [UNIQUEIDENTIFIER]          = NULL
WITH ENCRYPTION
AS
 
/*************************************************************************************************/ 
/* Name        : PU_Demo3                                                      */
/* Version     : 1.0                                                                             */
/* Author      : Jared Kirkpatrick                                                               */
/* Date        : 2019-08-24                                                                      */
/* Description :                                                                                 */
/*************************************************************************************************/ 
/* Date        : Version: Who: Description                                                       */
/*************************************************************************************************/ 
/* 2019-08-24  : 1.0    : JSK: Initial Release.                                                */
/*************************************************************************************************/ 
 
SET NOCOUNT ON;
 
/* Declare Variables */
DECLARE @v_Error                     [INT]
       ,@v_MSG                       [NVARCHAR](500)
       ,@v_DB                        [NVARCHAR]