USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPPROGRAMPAYMENTOPTIONS

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

    -- be sure to set this, in case the select returns no rows
    set @DATALOADED = 1;

    -- populate the output parameters, which correspond to fields on the form.  Note that
    -- we set @DATALOADED = 1 to indicate that the load was successful.  Otherwise, the system
    -- will display a "no data loaded" message.
set @PAYMENTOPTIONS = (
    select distinct
        MLT.RECURRINGPAYMENTOPTION,
      ML.MEMBERSHIPPROGRAMID
    from 
        MEMBERSHIPLEVELTERM MLT
        join MEMBERSHIPLEVEL ML on MLT.LEVELID = ML.ID
    where 
        ML.MEMBERSHIPPROGRAMID = @ID and MLT.ISACTIVE = 1
    for xml raw('ITEM'),type,elements,root('PAYMENTOPTIONS'),BINARY BASE64);

    return 0;