UFN_SPONSORSHIPOPPORTUNITY_AGEVALID

Indicates whether the specified opportunity birthdate is within its group's age range restriction.

Return

Return Type
tinyint

Parameters

Parameter	Parameter Type	Mode	Description
@SPONSORSHIPOPPORTUNITYGROUPID	uniqueidentifier	IN
@BIRTHDATE	UDT_FUZZYDATE	IN

Definition

Copy



CREATE function dbo.UFN_SPONSORSHIPOPPORTUNITY_AGEVALID(
    @SPONSORSHIPOPPORTUNITYGROUPID uniqueidentifier,
    @BIRTHDATE dbo.UDT_FUZZYDATE
)
returns tinyint  -- 0=too young, 1=within range, 2=too old

with execute as caller
as begin
    if @SPONSORSHIPOPPORTUNITYGROUPID is null
        return 1;

    declare @RESULT tinyint;
    declare @AGE int;
    set @AGE = dbo.UFN_AGEFROMFUZZYDATE(@BIRTHDATE,getdate());

    select @RESULT = case
             when @AGE < SPONSORSHIPOPPORTUNITYAGERANGE.MINAGE then 0
             when @AGE > SPONSORSHIPOPPORTUNITYAGERANGE.MAXAGE then 2
             else 1
           end
    from dbo.SPONSORSHIPOPPORTUNITYAGERANGE
    inner join dbo.SPONSORSHIPOPPORTUNITYGROUP on SPONSORSHIPOPPORTUNITYGROUP.SPONSORSHIPOPPORTUNITYAGERANGEID = SPONSORSHIPOPPORTUNITYAGERANGE.ID
    where SPONSORSHIPOPPORTUNITYGROUP.ID = @SPONSORSHIPOPPORTUNITYGROUPID;

  if @RESULT is null
    return 1;

    return @RESULT;
end