USP_BATCH_CONTROLANDEXCEPTIONREPORT_DELETE

CREATE procedure dbo.USP_BATCH_CONTROLANDEXCEPTIONREPORT_DELETE(
    @BATCHID uniqueidentifier)
with execute as owner
as
begin

    declare @BATCHCONTROLREPORTTABLENAME nvarchar(128);
    declare @BATCHEXCEPTIONREPORTTABLENAME nvarchar(128);

    select @BATCHCONTROLREPORTTABLENAME = dbo.UFN_BATCH_GETREPORTTABLENAME(@BATCHID, 0)
    select @BATCHEXCEPTIONREPORTTABLENAME = dbo.UFN_BATCH_GETREPORTTABLENAME(@BATCHID, 1)

    declare @SQLTOEXEC nvarchar(1000);

    set @SQLTOEXEC = N'IF object_id(''' + @BATCHCONTROLREPORTTABLENAME + ''') IS NOT NULL '
    set @SQLTOEXEC = @SQLTOEXEC + ' drop table dbo.' + @BATCHCONTROLREPORTTABLENAME

    exec sp_executesql @SQLTOEXEC

  --remove the reference to the output table from business process output

  delete from dbo.BUSINESSPROCESSOUTPUT 
  where BUSINESSPROCESSOUTPUT.BUSINESSPROCESSSTATUSID in (select ID from dbo.BATCHSTATUS where BATCHSTATUS.PARAMETERSETID = @BATCHID)
  and BUSINESSPROCESSOUTPUT.TABLEKEY = 'CONTROL'

    set @SQLTOEXEC = N'IF object_id(''' + @BATCHEXCEPTIONREPORTTABLENAME + ''') IS NOT NULL '
    set @SQLTOEXEC = @SQLTOEXEC + ' drop table dbo.' + @BATCHEXCEPTIONREPORTTABLENAME

    exec sp_executesql @SQLTOEXEC

  delete from dbo.BUSINESSPROCESSOUTPUT 
  where BUSINESSPROCESSOUTPUT.BUSINESSPROCESSSTATUSID in (select ID from dbo.BATCHSTATUS where BATCHSTATUS.PARAMETERSETID = @BATCHID)
  and BUSINESSPROCESSOUTPUT.TABLEKEY = 'EXCEPTION'

end