USP_IMPORTPROCESSBATCH_UPDATE

CREATE procedure dbo.USP_IMPORTPROCESSBATCH_UPDATE
(
  @ID uniqueidentifier,
  @NUMBEROFEXCEPTIONS int,
  @CHANGEAGENTID uniqueidentifier
)
as
begin
  set nocount on;

  if @CHANGEAGENTID is null  
      exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

  declare @BASETABLENAME nvarchar(128);
    declare @AMOUNTCOLUMN nvarchar(100);
  declare @BATCHID uniqueidentifier;
  declare @COMMITTED bit;
  declare @EXCEPTIONBATCHID uniqueidentifier;

    select @BASETABLENAME=BASETABLENAME, 
    @AMOUNTCOLUMN=AMOUNTCOLUMN, 
    @BATCHID = BATCH.ID, 
    @COMMITTED = case BATCH.STATUSCODE when 1 then 1 else 0 end, 
    @EXCEPTIONBATCHID = EXCEPTIONBATCH.ID
  from dbo.BATCHTYPECATALOG 
    inner join dbo.BATCHTEMPLATE on BATCHTYPECATALOG.ID = BATCHTEMPLATE.BATCHTYPECATALOGID
  inner join dbo.BATCH on BATCHTEMPLATE.ID = BATCH.BATCHTEMPLATEID
  left join dbo.BATCH as EXCEPTIONBATCH on BATCH.ID = EXCEPTIONBATCH.ORIGINATINGBATCHID
  inner join dbo.IMPORTPROCESSBATCH on BATCH.ID = IMPORTPROCESSBATCH.BATCHID
  where IMPORTPROCESSBATCH.ID = @ID;

    if len(@BASETABLENAME) > 0
    begin
      declare @sql nvarchar(max);
        declare @batchidstring nvarchar(36);
    declare @importprocessbatchid nvarchar(36);
    declare @exceptionbatchidstring nvarchar(36);
        set @batchidstring = cast(@BATCHID as nvarchar(36));
    set @importprocessbatchid = cast(@ID as nvarchar(36));

    set @sql = 'update dbo.IMPORTPROCESSBATCH set CHANGEDBYID = ''' + cast(@CHANGEAGENTID as nvarchar(36)) + ''', DATECHANGED = getdate(), ';
    if @COMMITTED = 1
    begin
      if @EXCEPTIONBATCHID is not null
      begin
        set @exceptionbatchidstring = cast(@EXCEPTIONBATCHID as nvarchar(36));
        if len(@AMOUNTCOLUMN) > 0
          set @sql = @sql + 'COMMITTEDTOTAL = (select coalesce(sum(' + @AMOUNTCOLUMN + '), 0) from dbo.' + @BASETABLENAME + ' where BATCHID = ''' + @batchidstring + ''') - (select coalesce(sum(' + @AMOUNTCOLUMN + '), 0) from dbo.' + @BASETABLENAME + ' where BATCHID = ''' + @exceptionbatchidstring + '''), ';
        set @sql = @sql + 'COMMITTEDNUMBER = (select count(1) from dbo.' + @BASETABLENAME + ' where BATCHID = ''' + @batchidstring + ''') - (select count(1) from dbo.' + @BASETABLENAME + ' where BATCHID = ''' + @exceptionbatchidstring + '''), ';
      end
      else
      begin
        if len(@AMOUNTCOLUMN) > 0
          set @sql = @sql + 'COMMITTEDTOTAL = (select coalesce(sum(' + @AMOUNTCOLUMN + '), 0) from dbo.' + @BASETABLENAME + ' where BATCHID = ''' + @batchidstring + '''), ';
        set @sql = @sql + 'COMMITTEDNUMBER = (select count(1) from dbo.' + @BASETABLENAME + ' where BATCHID = ''' + @batchidstring + '''), ';
      end
    end
    if len(@AMOUNTCOLUMN) > 0
        set @sql = @sql + 'IMPORTEDTOTAL = (select coalesce(sum(' + @AMOUNTCOLUMN + '), 0) from dbo.' + @BASETABLENAME + ' where BATCHID = ''' + @batchidstring + '''), ';
    set @sql = @sql + 'IMPORTEDNUMBER = (select count(1) from dbo.' + @BASETABLENAME + ' where BATCHID = ''' + @batchidstring + ''') where IMPORTPROCESSBATCH.ID = ''' + @importprocessbatchid + '''';
        exec(@sql);
    end    

    update dbo.IMPORTPROCESSBATCH
  set NUMBEROFBATCHEXCEPTIONS = @NUMBEROFEXCEPTIONS
  where ID = @ID;
end