USP_DATALIST_PATRON_MEMBERSHIP

List the memberships assigned to a given patron.

Parameters

Parameter	Parameter Type	Mode	Description
@CONTEXTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.

Definition

Copy



CREATE procedure dbo.USP_DATALIST_PATRON_MEMBERSHIP(@CONTEXTID uniqueidentifier)
as
    set nocount on;

    select
        MEMBERSHIP.ID as ID,
        '' + dbo.UFN_MEMBERSHIPPROGRAM_GETNAME(MEMBERSHIP.MEMBERSHIPPROGRAMID) + ' - ' + dbo.UFN_MEMBERSHIPLEVEL_GETNAME(MEMBERSHIP.MEMBERSHIPLEVELID) as DESCRIPTION,
        case
            when (MEMBERSHIP.STATUSCODE = 0 and dateadd(month,MEMBERSHIPLEVEL.AFTEREXPIRATION,MEMBERSHIP.EXPIRATIONDATE) <  getdate()) then 3
            else MEMBERSHIP.STATUSCODE
        end as SORTCODE
    from dbo.MEMBER
        inner join dbo.MEMBERSHIP on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
        inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIP.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
    where
        MEMBER.CONSTITUENTID = @CONTEXTID and
        MEMBER.ISDROPPED = 0
    order by
        SORTCODE asc,
        MEMBERSHIP.EXPIRATIONDATE asc