Allow a user defined stored procedure template #11
Description
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