USP_BATCH_DISMISSEXCEPTIONS

CREATE procedure dbo.USP_BATCH_DISMISSEXCEPTIONS (
    @BATCHID uniqueidentifier,
    @EXCEPTIONID uniqueidentifier = null,
    @CHANGEAGENTID uniqueidentifier = null
)
with execute as owner
as
begin

    declare @BASETABLENAME nvarchar(200);

    select @BASETABLENAME = BATCHTYPECATALOG.BASETABLENAME
    from dbo.BATCH
    inner join dbo.BATCHTEMPLATE on BATCH.BATCHTEMPLATEID = BATCHTEMPLATE.ID
    inner join dbo.BATCHTYPECATALOG on BATCHTEMPLATE.BATCHTYPECATALOGID = BATCHTYPECATALOG.ID
    where BATCH.ID = @BATCHID

    declare @sql nvarchar(1000);
    declare @paramdef nvarchar(1000);

    -- OriginCode 3 = import. When dismissing all exceptions in the batch, only worry about the ones

    -- which cannot be resolved automatically, i.e. the exceptions carring an invalid value from import




    set @sql = N'delete ' + quotename(@BASETABLENAME + 'BATCHSYSTEMMESSAGES') + 
        ' from dbo.' + quotename(@BASETABLENAME + 'BATCHSYSTEMMESSAGES') + ' as MSG' +
        ' inner join dbo.' + quotename(@BASETABLENAME) + ' as BASE on BASE.ID = ' + quotename(@BASETABLENAME + 'ID') +
        ' where BATCHID = @BATCHID and ' + 
        '((@EXCEPTIONID is null and ORIGINCODE = 3) or @EXCEPTIONID = MSG.ID)' 


    set @paramdef = N'@BATCHID uniqueidentifier, @EXCEPTIONID uniqueidentifier';

    declare @contextCache varbinary(128);

    /* cache current context information */
    set @contextCache = CONTEXT_INFO();

    /* set CONTEXT_INFO to @CHANGEAGENTID */
    if not @CHANGEAGENTID is null
        set CONTEXT_INFO @CHANGEAGENTID


    exec sp_executesql @sql, @paramdef, @BATCHID = @BATCHID, @EXCEPTIONID = @EXCEPTIONID;

    /* reset CONTEXT_INFO to previous value */
    if not @contextCache is null
        set CONTEXT_INFO @contextCache

end