UFN_MEMBER_VALIDALLOWMULTIPLEMEMBERSHIPS

Validates if a members is allowed to be added according to the allow multiple memberships bit in the membership program associated with the membership.

Return

Return Type
bit

Parameters

Parameter	Parameter Type	Mode	Description
@CONSTITUENTID	uniqueidentifier	IN
@MEMBERSHIPID	uniqueidentifier	IN

Definition

Copy



CREATE function [dbo].[UFN_MEMBER_VALIDALLOWMULTIPLEMEMBERSHIPS] 
(
    @CONSTITUENTID uniqueidentifier,
    @MEMBERSHIPID uniqueidentifier
)
returns bit
as begin
    declare @ALLOWMULTIPLEMEMBERSHIPS bit;
    declare @MEMBERSHIPPROGRAM uniqueidentifier;
    declare @STATUSCODE tinyint;

    select
        @ALLOWMULTIPLEMEMBERSHIPS = MP.ALLOWMULTIPLEMEMBERSHIPS,
        @MEMBERSHIPPROGRAM = M.MEMBERSHIPPROGRAMID,
        @STATUSCODE = M.STATUSCODE
    from
        dbo.MEMBERSHIPPROGRAM MP
    inner join
        dbo.MEMBERSHIP M on M.MEMBERSHIPPROGRAMID = MP.ID
    where
        M.ID = @MEMBERSHIPID;

    -- If the program does not allow multiple memberships... check and see if this

    --   constituent is already associated with the program.

    if @ALLOWMULTIPLEMEMBERSHIPS = 0 and @STATUSCODE <> 1  -- Cancelled

    begin
        if exists
        (
            select 1
            from dbo.MEMBER
            inner join dbo.MEMBERSHIP
            on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
            where @CONSTITUENTID = MEMBER.CONSTITUENTID
                and @MEMBERSHIPPROGRAM = MEMBERSHIP.MEMBERSHIPPROGRAMID
                and @MEMBERSHIPID <> MEMBERSHIP.ID
                and MEMBERSHIP.STATUSCODE = 0
                and MEMBER.ISDROPPED <> 1
        )
            return 0;
    end

    return 1;
end