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;
GOThe Input Parameter
You’ll notice first that our Template has an input parameter called @p_Batch_ID and it is set to NULL.
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.
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.
- PU_Demo1 – the Wrapper will print PU_Demo1 and then call procedures PU_Demo2 and PU_Demo3
- PU_Demo2 – the first nested procedure will print PU_Demo2 and exit back to the wrapper
- 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 LOGThe 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](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].[PU_Demo3]'
,@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
SELECT 'This is PU_Demo3'
RAISERROR ('Message', 19, 1) WITH LOG
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 PU_Demo3 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].[PU_Demo3]') IS NOT NULL
BEGIN
PRINT '>>> CREATED PROCEDURE [dbo].[PU_Demo3] <<<';
END;
ELSE
BEGIN
PRINT '>>> FAILED TO CREATE PROCEDURE [dbo].[PU_Demo3] <<<';
END;
GOThe next procedure PU_Demo2 we will copy the template code to a new query window, press CTRL+SHIFT+M, and type the name PU_Demo2. Go down to the middle of the procedure where it says:
==>> Start Code Here!
And replace that with:
SELECT 'This is PU_Demo2'The complete code for PU_Demo2 is below:
USE DBCATCH
GO
IF EXISTS(SELECT 1
FROM [dbo].[sysobjects] (NOLOCK)
WHERE [id] = OBJECT_ID(N'[dbo].[PU_Demo2]')
AND OBJECTPROPERTY([id], N'IsProcedure') = 1)
BEGIN
DROP PROCEDURE [dbo].[PU_Demo2];
PRINT '>>> DROPPED PROCEDURE [dbo].[PU_Demo2] <<<';
END
GO
CREATE PROC [dbo].[PU_Demo2]
@p_Batch_ID [UNIQUEIDENTIFIER] = NULL
WITH ENCRYPTION
AS
/*************************************************************************************************/
/* Name : PU_Demo2 */
/* Version : 1.0 */
/* Author : Jared Kirkpatrick */
/* Date : 2019-05-24 */
/* Description : */
/*************************************************************************************************/
/* Date : Version: Who: Description */
/*************************************************************************************************/
/* 2019-05-24 : 1.0 : JSK: 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].[PU_Demo2]'
,@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
SELECT 'This is PU_Demo2'
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 [PU_Demo2] 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].[PU_Demo2]') IS NOT NULL
BEGIN
PRINT '>>> CREATED PROCEDURE [dbo].[PU_Demo2] <<<';
END;
ELSE
BEGIN
PRINT '>>> FAILED TO CREATE PROCEDURE [dbo].[PU_Demo2] <<<';
END;
GOExecuting PU_Demo1
The last procedure (which is really the first procedure/wrapper) will call the other two procedures. To create PU_Demo1, copy the template to a new query window, press CTRL_SHIFT+M, and then replace ==>> Start Code Here! with the following commands:
SELECT 'This is PU_Demo1';
EXEC PU_Demo2
@p_Batch_ID = @p_Batch_ID;
EXEC PU_Demo3
@p_Batch_ID = @p_Batch_ID;I will show the complete code in a moment below, but first look at the code snippet above. We will execute PU_Demo1 which will have a NULL @p_Batch_ID.
- It will then call PU_Batch_ADD which will insert a record for the Batch table
- Assigns a value to @p_Batch_ID which we will receive back in the PU_Demo1 scope as an output param
- Sets the @v_Batchstatus to 1
- Will call PU_BatchDetail_ADD that will insert a record in the batch detail table
- Call PU_Demo2
- Will call PU_BatchDetail_ADD that will insert a record in the batch detail table
- Call PU_BatchDetailUPD that will update the batchdetail record with the end date
- Call PU_Demo2
- Will call PU_BatchDetail_ADD that will insert a record in the batch detail table
- Call PU_BatchDetailUPD that will update the batchdetail record with the end date
- Call PU_BatchDetailUPD that will update the batchdetail record with the end date
- Update TU_Batch and close the batch with an end date.
Here is the code for PU_Demo1 shown below:
USE DBCATCH
GO
IF EXISTS(SELECT 1
FROM [dbo].[sysobjects] (NOLOCK)
WHERE [id] = OBJECT_ID(N'[dbo].[PU_Demo1]')
AND OBJECTPROPERTY([id], N'IsProcedure') = 1)
BEGIN
DROP PROCEDURE [dbo].[PU_Demo1];
PRINT '>>> DROPPED PROCEDURE [dbo].[PU_Demo1] <<<';
END
GO
CREATE PROC [dbo].[PU_Demo1]
@p_Batch_ID [UNIQUEIDENTIFIER] = NULL
WITH ENCRYPTION
AS
/*************************************************************************************************/
/* Name : PU_Demo1 */
/* Version : 1.0 */
/* Author : Jared Kirkpatrick */
/* Date : 2019-05-24 */
/* Description : */
/*************************************************************************************************/
/* Date : Version: Who: Description */
/*************************************************************************************************/
/* 2019-05-24 : 1.0 : JSK: 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].[PU_Demo1]'
,@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
SELECT 'This is PU_Demo1';
EXEC PU_Demo2
@p_Batch_ID = @p_Batch_ID;
EXEC PU_Demo3
@p_Batch_ID = @p_Batch_ID;
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 [PU_Demo1] 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].[PU_Demo1]') IS NOT NULL
BEGIN
PRINT '>>> CREATED PROCEDURE [dbo].[PU_Demo1] <<<';
END;
ELSE
BEGIN
PRINT '>>> FAILED TO CREATE PROCEDURE [dbo].[PU_Demo1] <<<';
END;
GONow we execute PU_Demo1. Open a new window and type:
EXEC PU_Demo1Then hit ALT+X or click the execute button. Your results should look like the following:
So far, so good. The output is exactly what we expected. Now, look at the framework tables and validate the framework works as we expect it to.
First, check the TU_Errorlog and TU_ActivityLog tables:
I would normally get very excited here. We have one error that we coded for PU_Demo3 that showed up in the TU_Errorlog and in the activity log for object PU_Demo3. We already tested this functionality in the last article, so we will reserve the happy dance until we see the batching work.
Last, check the TU_Batch and TU_BatchDetail tables to validate our the last of our feature list:
Very Happy Outcomes
Now we are very happy. We have a record in the batch table and 1 in each of the corresponding batchdetail records grouped together with the batch_id. It shows us a nice ranking (which we could get just by ordering on the batchdetail_id groubed by the Batch_ID. It’s a nice elevator pitch that may get you some eye candy from management. The Object_ID will be the same activity_ID’s in the TU_activitylog table. We can see just how long each segment ran and we can see how long the batch took to run.
We have successfully created a functional framework with three key features. It would help any developer start rapid code development within this T-SQL framework. The first two features are a must-have for any piece of code that you want to take to an enterprise level. The batching of procedures is nice but is was picked as a feature for a few reasons:
- You can expand the feature set. Continue this development and create the next step, which would be to extend batching to also accommodate record set batching. Create a roadmap and write down your vision of how it should work and start developing. Go to it and have fun doing it!
- To show you that if you can dream it, you can build it. Don’t let my small introduction limit your T-SQL framework. You are limited only by your imagination.
- Because I thought it was a pretty cool feature, so I implemented it. You have a cool idea…go implement it and then write about it and share it.
I hope this series provided some insight into how you can make the day-to-day easier with some development. It also takes the load off the mundane, tedious tasks so that you can get to the good stuff — developing hard core logic?
