USP_BATCH_DELETE

CREATE procedure dbo.[USP_BATCH_DELETE]
(
  @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier
)
with execute as owner
as
  set nocount on;

  declare @LOCKNAME nvarchar(36);
  declare @RESULT int;
  declare @GRANTED bit;
  declare @BASETABLE nvarchar(128);
  declare @STATUSCODE tinyint;
  declare @PARENTCOMMITTED bit;

  begin try
    set @LOCKNAME = upper(cast(@ID as nvarchar(36)));
    exec @RESULT = sp_getapplock @Resource=@LOCKNAME, @LockMode='Exclusive', @LockOwner='Session', @LockTimeout=0;

    if @RESULT = 0
      begin
        set @GRANTED = dbo.[UFN_SECURITY_APPUSER_GRANTED_BATCHOWNER](@CURRENTAPPUSERID, @ID);
        if @GRANTED = 0
          raiserror('You do not have permission to delete this batch.', 13, 1);

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

        declare @CONTEXTCACHE varbinary(128);
        set @CONTEXTCACHE = CONTEXT_INFO();

        if not @CHANGEAGENTID is null
          set CONTEXT_INFO @CHANGEAGENTID;

        --Get the base tablename for this batch type...

        select
          @BASETABLE = [BATCHTYPECATALOG].[BASETABLENAME],
          @STATUSCODE = [BATCH].[STATUSCODE],
          @PARENTCOMMITTED = (case when [PARENTBATCH].[STATUSCODE] not in (1, 2) then 0 else 1 end)
        from dbo.[BATCH]
        inner join dbo.[BATCHTEMPLATE] on [BATCH].[BATCHTEMPLATEID] = [BATCHTEMPLATE].[ID]
        inner join dbo.[BATCHTYPECATALOG] on [BATCHTEMPLATE].[BATCHTYPECATALOGID] = [BATCHTYPECATALOG].[ID]
        left join dbo.[BATCH] as [PARENTBATCH] on [PARENTBATCH].[ID] = [BATCH].[ORIGINATINGBATCHID]
        where [BATCH].[ID] = @ID;

        if @STATUSCODE = 3
          raiserror('This batch is in a state which cannot be deleted.', 13, 1);

        if @PARENTCOMMITTED = 0
          raiserror('BBERR_BATCH_HASUNCOMMITTEDPARENT', 13, 1);

        --delete from batchtable

        declare @SQL nvarchar(max);
        set @SQL = 'delete from dbo.[' + @BASETABLE + '] where [BATCHID] = @BATCHID';
        exec sp_executesql @SQL, N'@BATCHID uniqueidentifier', @BATCHID = @ID;

        --mark batch as deleted

        update dbo.[BATCH] set
          [STATUSCODE] = 2,
          [CHANGEDBYID] = @CHANGEAGENTID,
          [DATECHANGED] = getdate()
        where [ID] = @ID;

        --Delete ignore duplicate list

        delete from dbo.[BATCHIGNOREDUPLICATE]
        where [BATCHID] = @ID;

        if not @CONTEXTCACHE is null
          set CONTEXT_INFO @CONTEXTCACHE;

        exec sp_releaseapplock @Resource=@LOCKNAME, @LockOwner='Session';
      end
    else
      raiserror('This batch is in use and cannot be deleted.', 13, 1);
  end try

  begin catch
    if len(@LOCKNAME) > 0 and @RESULT = 0
      exec sp_releaseapplock @Resource=@LOCKNAME, @LockOwner='Session';

    exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

  return 0;