Stored Procedure for Markit EDM Realign

spDmRealign

if exists (select 1 from INFORMATION_SCHEMA.ROUTINES where ROUTINE_SCHEMA = 'dbo' and ROUTINE_NAME = 'spDmRealign')
  drop procedure dbo.spDmRealign
go

create procedure dbo.spDmRealign
  @oldCadisId int,
  @newCadisId int,   /* Set @newCadisId = 0 below to get a *new* CadisID generated and assigned, or alternatively set to an existing target CadisID */
  @matcherName nvarchar(100),
  @sourceName nvarchar(100)
as begin
  declare @changedBy nvarchar(100) = current_user,
          @changedDate datetime = getdate(),
          @assignedCadisId int,
          @matcherId int,
          @sourceId int,
          @sql nvarchar(4000),
          @paramDefs nvarchar(500)
    
  select @matcherId = IDENTIFIER
  from CADIS_SYS.DM_MATCHPOINT
  where [NAME] = @matcherName
  and OBSOLETE = 0
  
  if isnull(@matcherId, -99) = -99
  begin
      print ('No active Matcher found with the name ' + @matcherName)
      return
  end
  
  print ('Processing matcher: ' + @matcherName)
  
  select @sourceId = SOURCEID
  from CADIS_SYS.DM_SOURCE
  where MATCHPOINTID = @matcherId
  and LONGNAME = @sourceName
  and OBSOLETE = 0
  
  if isnull(@sourceId, -99) = -99
  begin
    print ('No active Source found with name ' + @sourceName)
    return
  end 
  
  print ('Processing Source: ' + @sourceName)
  
  set @sql = 'EXEC [CADIS_PROC].[SPDM_MP' + CONVERT(NVARCHAR, @matcherId) + '_SOURCE' + CONVERT(NVARCHAR, @sourceId) + '_REALIGN_MATCH] '
  set @sql = @sql + '@OldCADISId, @NewCADISId, @ChangedBy, @ChangedDate, @assignedCadisId OUTPUT'
  set @paramDefs = '@oldCadisId INT, @newCadisId INT, @changedBy NVARCHAR(100), @changedDate DATETIME, @assignedCadisId INT OUTPUT'
   
  exec sp_executesql @sql, @paramDefs, @oldCadisId, @newCadisId, @changedBy, @changedDate, @assignedCADISId OUTPUT
  
  print ('Source realigned from CadisID ' + CONVERT(VARCHAR, @oldCadisId) + ' to CadisID ' + CONVERT(VARCHAR, @assignedCadisId))
  
end