USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPPROGRAMDUESBASEDRULES

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@DUESTREATEDASCONTRIBUTION	tinyint	INOUT
@ONEPAYMENTEACHTERM	tinyint	INOUT
@MULTIPLEPAYMENTSEACHTERM	tinyint	INOUT
@DESIGNATIONS	xml	INOUT
@PROGRAMTYPECODE	tinyint	INOUT
@INSTALLMENTPOSTSTATUSCODE	tinyint	INOUT

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPPROGRAMDUESBASEDRULES
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @DUESTREATEDASCONTRIBUTION tinyint = null output,
    @ONEPAYMENTEACHTERM tinyint = null output,
    @MULTIPLEPAYMENTSEACHTERM tinyint = null output,
    @DESIGNATIONS xml = null output,
    @PROGRAMTYPECODE tinyint = null output,
    @INSTALLMENTPOSTSTATUSCODE tinyint = null output
)
as
    set nocount on;

    -- be sure to set this, in case the select returns no rows

    set @DATALOADED = 0;

    select
        @DATALOADED = 1,
        @DUESTREATEDASCONTRIBUTION = MP.DUESTREATEDASCONTRIBUTION,
        @ONEPAYMENTEACHTERM = MP.ONEPAYMENTEACHTERM,
        @MULTIPLEPAYMENTSEACHTERM = MP.MULTIPLEPAYMENTSEACHTERM,
        @PROGRAMTYPECODE = MP.PROGRAMTYPECODE,
        @INSTALLMENTPOSTSTATUSCODE = MP.INSTALLMENTPOSTSTATUSCODE
    from 
        dbo.MEMBERSHIPPROGRAM MP
    where
     MP.ID = @ID

    set @DESIGNATIONS = (
        select
            MPD.ID,
            D.NAME as DESIGNATION,
            MPD.[PERCENT]
        from
            dbo.MEMBERSHIPPROGRAMDESIGNATION MPD    
            inner join dbo.DESIGNATION D on MPD.DESIGNATIONID = D.ID
        where
            MPD.MEMBERSHIPPROGRAMID = @ID
        for xml raw ('ITEM'), type, elements, root('DESIGNATIONS'), BINARY BASE64
    );

    return 0;