USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTMEMBERSHIPOTHERMEMBERSHIPS

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

       declare @CONSTITUENTID uniqueidentifier = (select CONSTITUENTID from dbo.MEMBER where MEMBER.MEMBERSHIPID = @ID and ISPRIMARY = 1 and ISDROPPED = 0)

       set @OTHERMEMBERSHIPS =
            (
                select top 3
                    MS.ID as MEMBERSHIPID,
                    MP.NAME as PROGRAMNAME,
                    MS.STATUS as STATUS,
                    count(*) over(partition by M.CONSTITUENTID) as OTHER_MEMBERSHIPS_CNT
                from dbo.MEMBERSHIP MS
                inner join dbo.MEMBERSHIPPROGRAM MP on MS.MEMBERSHIPPROGRAMID = MP.ID
                inner join dbo.MEMBER M on M.MEMBERSHIPID = MS.ID AND M.ISDROPPED = 0
                inner join dbo.MEMBER PRIMARYMEMBER on PRIMARYMEMBER.MEMBERSHIPID = MS.ID and PRIMARYMEMBER.ISPRIMARY = 1
                where
                    M.CONSTITUENTID = @CONSTITUENTID and
                    MS.ID <> @ID
                order by MS.DATEADDED
        for xml raw('ITEM'),type,elements,root('OTHERMEMBERSHIPS'),binary base64)

    return 0;