USP_GLOBALCHANGE_SPONSORSHIPOPPORTUNITYUNLOCKALL

CREATE procedure dbo.USP_GLOBALCHANGE_SPONSORSHIPOPPORTUNITYUNLOCKALL
(
    @CHANGEAGENTID uniqueidentifier = null,
    @ASOF as datetime = null,
    @NUMBERADDED int = 0 output,
    @NUMBEREDITED int = 0 output,
    @NUMBERDELETED int = 0 output,
  @TIMEOUTMINUTES smallint = 15
)
as            
    set nocount off;

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate();
    set @NUMBERADDED = 0;
    set @NUMBEREDITED = 0;
    set @NUMBERDELETED = 0; 

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

    begin try
        -- Clear all orphaned locks, using a timeout of 15 minutes.
    -- Ignore locks held by BBNC, which are linked to sales orders.
    ;with CTE as (
      select SO.ID, count(SOIS.SPONSORSHIPOPPORTUNITYID) LOCKCOUNT
      from dbo.SPONSORSHIPOPPORTUNITY SO
      left join dbo.SALESORDERITEMSPONSORSHIP SOIS on SOIS.SPONSORSHIPOPPORTUNITYID = SO.ID
      group by SO.ID
    )
        update SOL
        set LOCKCOUNT = CTE.LOCKCOUNT,
        LOCKED = 0,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
    from dbo.SPONSORSHIPOPPORTUNITYLOCK SOL
    inner join CTE on CTE.ID = SOL.ID
        where (SOL.LOCKCOUNT > CTE.LOCKCOUNT or
           (SOL.LOCKED = 1 and CTE.LOCKCOUNT = 0))
    and SOL.DATECHANGED < dateadd(minute, -1*@TIMEOUTMINUTES, @CURRENTDATE);

    set @NUMBEREDITED = @@ROWCOUNT;

    end try

    begin catch
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch