USP_BATCHCONSTITUENTUPDATE_DELETEBATCH

          CREATE procedure dbo.USP_BATCHCONSTITUENTUPDATE_DELETEBATCH
          (
              @ID uniqueidentifier,
              @CHANGEAGENTID uniqueidentifier,
              @CURRENTAPPUSERID uniqueidentifier = null
          )
          as begin
              set nocount on;

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

              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
                          if @CHANGEAGENTID is null
                              exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                          set CONTEXT_INFO @CHANGEAGENTID;

                          if @CURRENTAPPUSERID is not null
                              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);
                              end;

                              --remove solicit codes

                              delete    BSC
                              from        dbo.BATCHCONSTITUENTUPDATESOLICITCODE BSC
                              join        BATCHCONSTITUENTUPDATE on BATCHCONSTITUENTUPDATE.ID = BSC.BATCHCONSTITUENTUPDATEID
                              where     BATCHID = @ID

                              --remove BBIS transaction info if batch was created by BBIS signup transaction

                              declare @BATCHSTATUS int;
                              select @BATCHSTATUS = dbo.UFN_BATCH_GETSTATUSCODE (@ID);

                               --only transactions from open batches need to be downloaded again

                               If @BATCHSTATUS = 0
                               begin
                                  declare @BBNCTRANSACTIONIDS xml = (select * from dbo.UFN_BBNC_SIGNUP_GETROWBBNCIDS(@ID) for xml raw('INT'),type,root('TRANSACTIONIDS'),BINARY BASE64);
                                          exec dbo.USP_BBNC_TRANSACTION_DELETEBATCH @BBNCTRANSACTIONIDS;
                               end

                              delete from dbo.BATCHCONSTITUENTUPDATEBBNCINFO where BATCHCONSTITUENTUPDATEID = @ID;

                              exec sp_releaseapplock @Resource=@lockName, @LockOwner='Session';

                              if not @contextCache is null
                                  set CONTEXT_INFO @contextCache; 
                      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;

          end