USP_SPONSORSHIPOPPORTUNITY_LOCK

create procedure dbo.USP_SPONSORSHIPOPPORTUNITY_LOCK (
  @ID uniqueidentifier,
  @LOCKTYPE tinyint,  -- 0 = Shared

                      -- 1 = Exclusive

                      -- 2 = Standard (includes check for sole sponsorship)

                      -- 3 = Edit (exclusive, ignores eligibility/availability)

  @CHANGEAGENTID uniqueidentifier = null
)
as
begin
    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

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

  declare @SPONSORSPEROPPORTUNITY smallint;
  declare @OFFERSOLESPONSORSHIP bit;

    select @SPONSORSPEROPPORTUNITY = dbo.UFN_SPONSORSHIPOPPORTUNITY_SPONSORSPEROPPORTUNITY(O.SPONSORSHIPOPPORTUNITYGROUPID,O.SPONSORSHIPLOCATIONID),
         @OFFERSOLESPONSORSHIP = case @LOCKTYPE when 2 then case L.SPONSORCOUNT when 0 then dbo.UFN_SPONSORSHIP_OFFERSOLESPONSORSHIP(O.SPONSORSHIPOPPORTUNITYGROUPID,O.SPONSORSHIPLOCATIONID) else 0 end end
  from dbo.SPONSORSHIPOPPORTUNITY O
  inner join dbo.SPONSORSHIPOPPORTUNITYLOCK L on L.ID = O.ID
  where O.ID = @ID;

  -- Locktype 2 does not make it to the update statement.

  if @LOCKTYPE = 2
    set @LOCKTYPE = @OFFERSOLESPONSORSHIP;

  -- If the number of "locks" (i.e., the number of times an opportunity has been matched)

  -- exceeds the number of possible sponsorships for the opportunity, 

    -- reserve the opportunity w/ no key - this indicates a temporary reservation.

    update SOL
    set LOCKCOUNT = case @LOCKTYPE when 0 then SOL.LOCKCOUNT + 1 else 0 end,
      LOCKED = case @LOCKTYPE
                 when 1 then 1
                 when 3 then 1
                 else case when SOL.SPONSORCOUNT + SOL.LOCKCOUNT + 1 = @SPONSORSPEROPPORTUNITY then 1 else 0 end
               end,
        CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED = @CURRENTDATE
  from dbo.SPONSORSHIPOPPORTUNITY SO
  inner join dbo.SPONSORSHIPOPPORTUNITYLOCK SOL on SOL.ID = SO.ID
    where SO.ID = @ID
    and (@LOCKTYPE = 3 or
       (SO.ELIGIBILITYCODE = 1 and SO.AVAILABILITYCODE <> 2))
  and SOL.LOCKED = 0
  and (@LOCKTYPE = 0 or SOL.LOCKCOUNT = 0);

  if @@ROWCOUNT = 0
    raiserror('BBERR_NOLOCK',13,1)
end