USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPTRANSFER

The load procedure used by the view dataform template "Membership Transfer View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@MEMBERSHIPPROGRAMS	xml	INOUT	MEMBERSHIPPROGRAMS

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPTRANSFER
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @MEMBERSHIPPROGRAMS xml = null output
)
as
    set nocount on;

    set @DATALOADED = 1
    set @MEMBERSHIPPROGRAMS =
    (
        select distinct MEMBERSHIPPROGRAM.ID as MEMBERSHIPPROGRAMID,
            MEMBERSHIPPROGRAM.ALLOWMULTIPLEMEMBERSHIPS as ALLOWMULTIPLEMEMBERSHIPS,
            MEMBERSHIPPROGRAM.NAME as MEMBERSHIPPROGRAMNAME,
            case when exists
            (
                select 1
                from dbo.MEMBERSHIPCARD
                where MEMBER.ID = MEMBERSHIPCARD.MEMBERID and
                    MEMBERSHIPCARD.STATUSCODE <> 2
            )
                then 
                    1
                else 
                    0
                end as HASACTIVECARD
        from dbo.MEMBER
        inner join dbo.MEMBERSHIP on
            MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
        inner join dbo.MEMBERSHIPPROGRAM on
            MEMBERSHIPPROGRAM.ID = MEMBERSHIP.MEMBERSHIPPROGRAMID
        where MEMBER.CONSTITUENTID = @ID and 
            MEMBER.ISDROPPED = 0 and
            MEMBERSHIP.STATUSCODE <> 1
        for xml raw('ITEM'),type,elements,root('MEMBERSHIPPROGRAMS'),BINARY BASE64
    );


    return 0;