USP_SPONSORSHIPBATCH_DELETEBATCH

CREATE procedure dbo.USP_SPONSORSHIPBATCH_DELETEBATCH
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier
)
as begin

    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
            --remove the batch const group member first

            delete from dbo.BATCHSPONSORSHIPCONSTITUENTGROUPMEMBER
                             where GROUPID in 
                                    (select BSC.ID from dbo.BATCHSPONSORSHIPCONSTITUENT BSC
                                         inner join dbo.BATCHSPONSORSHIP BS on BS.CONSTITUENTID = BSC.ID
                                         where BS.BATCHID = @ID);

            --remove sponsorship constituent if the constituent was created in this batch

            delete from dbo.BATCHSPONSORSHIPCONSTITUENT
                    where ID in 
                        (select BSC.ID from dbo.BATCHSPONSORSHIPCONSTITUENT BSC
                            inner join dbo.BATCHSPONSORSHIP BS on BS.CONSTITUENTID = BSC.ID
                            where BS.BATCHID = @ID);

            delete from dbo.BATCHSPONSORSHIPCONSTITUENTACCOUNT
                where BATCHSPONSORSHIPCONSTITUENTACCOUNT.ID in
                    (
                        select CONSTITUENTACCOUNTID
                        from dbo.BATCHSPONSORSHIP
                        where BATCHSPONSORSHIP.BATCHID = @ID
                    );

            delete from dbo.BATCHREVENUE where BATCHID = @ID;
        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