USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPPROGRAMADDONS

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_MEMBERSHIPPROGRAMADDONS
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @ADDONS 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 @ADDONS = (SELECT 
                  MPA.ID,
                    A.NAME,
                  MPA.Description,
                    MPA.PRICE,
                    CASE WHEN MPA.MULTIPLEALLOWED = 0 THEN 'No' ELSE 'Yes' END MULTIPLEALLOWED
                FROM dbo.MEMBERSHIPPROGRAM MP
                INNER JOIN dbo.MEMBERSHIPPROGRAMADDON MPA on MP.ID = MPA.MEMBERSHIPPROGRAMID
                INNER JOIN dbo.ADDON A on MPA.ADDONID = A.ID    
                where MP.ID = @ID
            for xml raw ('ITEM'), type, elements, root('ADDONS'), BINARY BASE64);

    return 0;