USP_SPONSORSHIPOPPORTUNITY_RESERVE

CREATE procedure dbo.USP_SPONSORSHIPOPPORTUNITY_RESERVE
(
    @OPPORTUNITYID uniqueidentifier,
    @KEYID uniqueidentifier,
    @SUCCESSCOUNT int = 0 output,
    @EXCEPTIONCOUNT int = 0 output,
    @SUCCESSTABLE nvarchar(128),
    @EXCEPTIONTABLE nvarchar(128),
  @PENDING int = 1,
  @INELIGIBLE int = 1,
  @CHANGEAGENTID uniqueidentifier = null
)
as
begin
    declare @CURRENTDATE datetime;
  set @CURRENTDATE = getdate(); 

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

    declare @LOGSUCCESSSQL nvarchar(200)
    declare @LOGSUCCESSPARAMS nvarchar(75)
    declare @LOGEXCEPTIONSQL nvarchar(200)
    declare @LOGEXCEPTIONPARAMS nvarchar(75)

    set @LOGSUCCESSSQL = N'insert into dbo.' + @SUCCESSTABLE + N' (SPOPPORTUNITYID) ' +
                         N'values (@SPOPPORTUNITYID)'
    set @LOGSUCCESSPARAMS = N'@SPOPPORTUNITYID uniqueidentifier'

    set @LOGEXCEPTIONSQL = N'insert into dbo.' + @EXCEPTIONTABLE + N' (SPOPPORTUNITYID, ERRORMESSAGE) ' +
                           N'values (@SPOPPORTUNITYID,@ERRORMESSAGE)'
    set @LOGEXCEPTIONPARAMS = N'@SPOPPORTUNITYID uniqueidentifier,' +
                              N'@ERRORMESSAGE nvarchar(255)'

    set @SUCCESSCOUNT = 0    
    set @EXCEPTIONCOUNT = 0

    begin try  
    save transaction RESERVE

    -- change the key status

    update dbo.SPONSORSHIPOPPORTUNITYRESERVATIONSTATUS
    set STATUSCODE = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATE
    where OPPORTUNITYRESERVATIONKEYID = @KEYID and STATUSCODE <> 1

    -- update the opportunity

    update SO
    set RESERVATIONKEYID = @KEYID, AVAILABILITYCODE = 1, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATE
    from dbo.SPONSORSHIPOPPORTUNITY SO
    inner join dbo.SPONSORSHIPOPPORTUNITYLOCK SOL on SOL.ID = SO.ID
    where SO.ID = @OPPORTUNITYID
    and SO.AVAILABILITYCODE = 0
    and SO.ELIGIBILITYCODE in (1,@PENDING,@INELIGIBLE)
    and SOL.LOCKED = 0;

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

        -- log the successful transfer

      exec sp_executesql @LOGSUCCESSSQL,
                       @LOGSUCCESSPARAMS,
                       @OPPORTUNITYID

      set @SUCCESSCOUNT = @SUCCESSCOUNT + 1
    end try
    begin catch
    rollback transaction RESERVE

      declare @MSG nvarchar(255)
      set @MSG = ERROR_MESSAGE()

      exec sp_executesql @LOGEXCEPTIONSQL,
                         @LOGEXCEPTIONPARAMS,
                         @OPPORTUNITYID,
                         @MSG

      set @EXCEPTIONCOUNT = @EXCEPTIONCOUNT + 1
    end catch

    return 0

end