UFN_SPONSORSHIPOPPORTUNITY_OPPORTUNITYCOUNT

Function that returns available count by opportunity

Return

Return Type
smallint

Parameters

Parameter	Parameter Type	Mode
@SPONSORSHIPOPPORTUNITYID	uniqueidentifier	IN
@SPONSORSHIPOPPORTUNITYGROUPID	uniqueidentifier	IN
@SPONSORSHIPOPPORTUNITYLOCATIONID	uniqueidentifier	IN

Definition

Copy



create function dbo.UFN_SPONSORSHIPOPPORTUNITY_OPPORTUNITYCOUNT(
    @SPONSORSHIPOPPORTUNITYID uniqueidentifier,
    @SPONSORSHIPOPPORTUNITYGROUPID uniqueidentifier,
    @SPONSORSHIPOPPORTUNITYLOCATIONID uniqueidentifier
)
returns smallint
with execute as caller
as begin
    declare @SPONSOREDCOUNT smallint
    declare @ISSOLESPONSORSHIP tinyint
    declare @SPONSORSPEROPPORTUNITY smallint
    declare @RETURNVAL smallint
    -- do work here and return a value


    set @SPONSORSPEROPPORTUNITY = dbo.UFN_SPONSORSHIPOPPORTUNITY_SPONSORSPEROPPORTUNITY(@SPONSORSHIPOPPORTUNITYGROUPID,@SPONSORSHIPOPPORTUNITYLOCATIONID)

    select 
        @ISSOLESPONSORSHIP = ISSOLESPONSORSHIP,
        @SPONSOREDCOUNT = COUNT(*)
     from dbo.SPONSORSHIP
    where SPONSORSHIPOPPORTUNITYID = @SPONSORSHIPOPPORTUNITYID
      and STATUSCODE in (0,1)
    group by ISSOLESPONSORSHIP

    if @ISSOLESPONSORSHIP = 1
        set @RETURNVAL = 0
    else
    begin
        if @SPONSOREDCOUNT is null
            set @RETURNVAL = @SPONSORSPEROPPORTUNITY
        else
            set @RETURNVAL = @SPONSORSPEROPPORTUNITY - @SPONSOREDCOUNT
    end
    return @RETURNVAL
end