UFN_MEMBERSHIPSTATUS_GETVALUE

Returns the membership status value(includes lapsed)

Return

Return Type
nvarchar(9)

Parameters

Parameter	Parameter Type	Mode	Description
@MEMBERSHIPID	uniqueidentifier	IN

Definition

Copy



            CREATE function dbo.UFN_MEMBERSHIPSTATUS_GETVALUE
            (
                @MEMBERSHIPID uniqueidentifier
            )
            returns nvarchar(9)
            as
            begin
                declare @TODAY date;
                set @TODAY = getdate();
                declare @STATUSVALUE nvarchar(9);
                select  @STATUSVALUE = case 
                        when (MEMBERSHIP.STATUSCODE = 0 and MEMBERSHIP.EXPIRATIONDATE <  @TODAY) then 'Lapsed'
                        else MEMBERSHIP.STATUS
                        end
                from dbo.MEMBERSHIP 
                inner join dbo.MEMBERSHIPLEVEL as LEVEL on
                    MEMBERSHIP.MEMBERSHIPLEVELID = LEVEL.ID
                where MEMBERSHIP.ID=@MEMBERSHIPID;
                return @STATUSVALUE;
            end