Allow a user defined stored procedure template

[jwk6]

Allow a user defined stored procedure template so that the developer can inject other T-SQL code around the merge statement.

For example, I use Azure Data Factory to call a stored procedure like this:

``

--Description:    Merges changes to the [[TableName]] table
--Author:         jwk6
--Create Date:    08/06/2020
--Changed By:  
CREATE PROCEDURE [dbo].[usp_[[TableName]]_Merge]
    @ExecutionID NVARCHAR(90)
AS
BEGIN

    DECLARE @CurrentDateTime DATETIME = GETUTCDATE()
    DECLARE @InsertOperation NVARCHAR(1) = N'I'
    DECLARE @UpdateOperation NVARCHAR(1) = N'U'
    DECLARE @InsertCount INT = 0
    DECLARE @UpdateCount INT = 0
    DECLARE @DeleteCount INT = 0

    DECLARE @Changes TABLE(
        [Operation] NVARCHAR(10));

    DECLARE @ChangeSummary TABLE(
        [Operation] NVARCHAR(10),
        [RecordCount] INT);

    /*body of the merge statement goes here */
    OUTPUT $action into @Changes;

    INSERT INTO @ChangeSummary (
            [Operation], 
            [RecordCount])
    SELECT  Operation, COUNT(*) AS OperationCount
    FROM	@Changes
    GROUP BY Operation;
    
    SET @InsertCount = (SELECT [RecordCount] FROM @ChangeSummary WHERE [Operation] = 'INSERT');
    SET @UpdateCount = (SELECT [RecordCount] FROM @ChangeSummary WHERE [Operation] = 'UPDATE');
    SET @DeleteCount = (SELECT [RecordCount] FROM @ChangeSummary WHERE [Operation] = 'DELETE');

    EXEC usp_Audit_Update '[[TableName]]',
                        @ExecutionID,
                        @InsertCount,
                        @UpdateCount,
                        @DeleteCount

END
GO

[NowinskiK]

@jwk6, do you want to do this?

[jwk6]

@NowinskiK Yeah, sure! I think I'll have some time and I'm feeling the urge again. ;-)