Generating Rollback Proc with CDC

DECLARE @schemaName VARCHAR(35)
DECLARE @tableName VARCHAR(100)
DECLARE @fullSQL VARCHAR(MAX)
DECLARE @tempSQL VARCHAR(MAX)

SET @schemaName = 'Person'
SET @tableName = 'Contact'

IF object_id('cdc.'+@schemaName+'_'+@tableName+'_CT') IS NULL
BEGIN
    PRINT 'Table Does Not Have CDC enabled'
    PRINT 'To enable this table run this command:'
    PRINT 'sys.sp_cdc_enable_table @source_schema = '''+@schemaName+''', 
 @source_name = '''+@tableName +''', 
 @supports_net_changes = 1, 
 @role_name = ''dbo''' 
    RETURN
END

IF object_id('Rollback_'+@schemaName+'_'+@tableName) IS NOT NULL
BEGIN
    PRINT 'Stored Procedure Already Exists'
    RETURN
end

SET @fullSQL = 'CREATE PROCEDURE Rollback_'+@schemaName+'_'+@tableName+' 
@startTime datetime, 
@endTime datetime 
AS
--USAGE: Rollback_'+@schemaName+'_'+@tableName+' @startTime, @endTime
--You can use the following query to find the times of the transactions
--you are looking for:
--SELECT sys.fn_cdc_map_lsn_to_time(__$start_lsn), *
--FROM cdc.'+@schemaName+'_'+@tableName+'_CT CT 
--ORDER BY [__$start_lsn] DESC, [__$seqval] DESC


DECLARE @start_lsn BINARY(10)
DECLARE @seqval BINARY(10)
DECLARE @action INT


IF @startTime IS NULL
    SET @startTime = GETDATE()
IF @endTime IS null 
    SET @endTime = GETDATE() 
    
'



SET @tempSQL = 'DECLARE tranSteps CURSOR FOR
SELECT __$start_lsn, [__$seqval], [__$operation] 
FROM cdc.'+@schemaName+'_'+@tableName+'_CT PAC 
WHERE sys.fn_cdc_map_lsn_to_time(__$start_lsn) >= @startTime
AND sys.fn_cdc_map_lsn_to_time(__$start_lsn) <= @endTime
AND [__$operation] < 4 
ORDER BY [__$start_lsn] DESC, [__$seqval] desc'

SET @fullSQL = @fullSQL + @tempSQL + '

OPEN tranSteps

FETCH NEXT FROM tranSteps INTO @start_lsn, @seqval, @action
WHILE @@fetch_status = 0
BEGIN
    IF @action = 1 --delete
    BEGIN
        SET IDENTITY_INSERT '+@schemaName+'.'+@tableName +' ON 
        '
        
;WITH tableColumns AS 
(
SELECT SCHEMA_NAME(O.schema_id) AS [SCHEMA_NAME], OBJECT_NAME(O.object_id) AS table_name, dbo.CombiningRowsForCDC(SCHEMA_NAME(O.schema_id),OBJECT_NAME(O.object_id)) AS column_list
FROM cdc.change_tables CT
INNER JOIN sys.objects O ON CT.source_object_id = O.object_id
INNER JOIN cdc.captured_columns CC ON CT.object_id = CC.object_id
WHERE SCHEMA_NAME(O.schema_id) = @schemaName
AND OBJECT_NAME(O.object_id) = @tableName
GROUP BY SCHEMA_NAME(O.schema_id), OBJECT_NAME(O.object_id)
)        
SELECT @tempSQL = 'insert into ' + [SCHEMA_NAME]+'.'+[table_name] + '('+ column_list +') 
    select ' + column_list + ' 
    from cdc.' + [SCHEMA_NAME] + '_' + [table_name] + '_CT 
    where sys.fn_cdc_map_lsn_to_time(__$start_lsn) >= @startTime'
FROM tableColumns TC
WHERE TC.[SCHEMA_NAME] = @schemaName
AND TC.[table_name] = @tableName

SET @fullSQL = @fullSQL + @tempSQL + '
         SET IDENTITY_INSERT '+@schemaName+'.'+@tableName+ ' OFF
    END
    ELSE IF @action = 2 --insert 
    BEGIN
    
        delete from ' + @schemaName + '.' + @tableName + '
            where 1 = 1 '
DECLARE @indexColumn VARCHAR(100)
    
DECLARE indexColsCur CURSOR FOR
SELECT IC.column_name
FROM cdc.change_tables CT
INNER JOIN sys.objects O ON CT.source_object_id = O.object_id
INNER JOIN cdc.index_columns IC ON CT.object_id = IC.object_id
WHERE SCHEMA_NAME(O.schema_id) = @schemaName
AND OBJECT_NAME(O.object_id) = @tableName

OPEN indexColsCur

FETCH NEXT FROM indexColsCur INTO @indexColumn
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @tempSQL = '
    and ' + @indexColumn + ' = (SELECT ' + @indexColumn + '
        from cdc.'+@schemaName+'_'+@tableName + '_CT 
        WHERE [__$start_lsn] = @start_lsn
        AND [__$seqval] = @seqval)'
SET @fullSQL = @fullSQL + @tempSQL
FETCH NEXT FROM indexColsCur INTO @indexColumn
end        

CLOSE indexColsCur


SET @fullSQL = @fullSQL + '
    END
    ELSE IF @action = 3 --update (old values)
    BEGIN
    
    UPDATE TargetTable
    SET
    '
    
DECLARE @column VARCHAR(100)
DECLARE columnsCursor CURSOR FOR
SELECT CC.column_name
FROM cdc.change_tables CT
INNER JOIN sys.objects O ON CT.source_object_id = O.object_id
INNER JOIN cdc.captured_columns CC ON CT.object_id = CC.object_id
LEFT JOIN cdc.index_columns IC ON CC.object_id = IC.object_id AND CC.column_id = IC.column_id
WHERE SCHEMA_NAME(O.schema_id) = @schemaName
AND OBJECT_NAME(O.object_id) = @tableName
AND IC.column_id IS null

OPEN columnsCursor

FETCH NEXT FROM columnsCursor INTO @column
WHILE @@FETCH_STATUS = 0
BEGIN
    
    SET @tempSQL = 'TargetTable.'+@column+' = CT.'+@column

FETCH NEXT FROM columnsCursor INTO @column
IF @@FETCH_STATUS = 0
    BEGIN
        SET @tempSQL = @tempSQL + ',
        '
    END
SET @fullSQL = @fullSQL + @tempSQL
end    

CLOSE columnsCursor
DEALLOCATE columnsCursor

SET @fullSQL = @fullSQL + '
        FROM '+@schemaName+'.'+@tableName+ ' TargetTable
        INNER JOIN cdc.'+@schemaName+'_'+@tableName+'_CT CT ON '

OPEN indexColsCur

FETCH NEXT FROM indexColsCur INTO @indexColumn
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @tempSQL = '
    TargetTable.' + @indexColumn + ' = CT.' + @indexColumn
FETCH NEXT FROM indexColsCur INTO @indexColumn
IF @@FETCH_STATUS = 0
    BEGIN
        SET @tempSQL = @tempSQL + ' and'
    END
SET @fullSQL = @fullSQL + @tempSQL
end        

CLOSE indexColsCur
DEALLOCATE indexColsCur

SET @fullSQL = @fullSQL + '        
        WHERE CT.__$start_lsn = @start_lsn
        AND CT.__$seqval = @seqval
    END
    ELSE
    PRINT ''This is never supposed to happen''

    FETCH NEXT FROM tranSteps INTO @start_lsn, @seqval, @action
END

SELECT GETDATE() --just so you know the last time 

CLOSE tranSteps
DEALLOCATE tranSteps
' 

EXECUTE (@fullSQL)
--PRINT @fullSQL