USP_REGISTRANTBATCH_DELETE

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

begin try

    declare @lockName nvarchar(36);
    set @lockName = upper(cast(@ID as nvarchar(36)));

    declare @result int;

    exec @result = sp_getapplock @Resource=@lockName, @LockMode='Exclusive', @LockOwner='Session', @LockTimeout=0;

    if @result = 0
        begin
            declare @GRANTED bit
            select @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 basetablename

            declare @basetable nvarchar(128);
            select @basetable=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 = @ID;


      --delete from EventBatch table

      delete from dbo.EVENTBATCH where ID = @ID;

            --delete from batchtable

            declare @sql nvarchar(200)
            set @sql = 'delete from dbo.' + @basetable + ' where BATCHID = ''' + cast(@ID as nvarchar(36)) + '''';
            exec(@sql);

            --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 ID = @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
    exec dbo.USP_RAISE_ERROR;
    return 1;
end catch

return 0;