USP_DATALIST_MARRIAGERELATIONSHIP

CREATE procedure dbo.USP_DATALIST_MARRIAGERELATIONSHIP
as
set nocount on;

select 
  MARRIAGERELATIONSHIP.ID,
    (
    select 
      dbo.UDA_BUILDLIST(RELATIONSHIPTYPECODE.DESCRIPTION)
    from dbo.MARRIAGERELATIONSHIPASSOCIATION
    inner join dbo.RELATIONSHIPTYPECODE
          on RELATIONSHIPTYPECODE.ID = MARRIAGERELATIONSHIPASSOCIATION.RELATIONSHIPTYPECODEID
    where MARRIAGERELATIONSHIPASSOCIATION.MARRIAGERELATIONSHIPID = MARRIAGERELATIONSHIP.ID
  ) AS ASSOCIATIONS,
    case when SPOUSEGENDERCODE = 1 then 'Male' when SPOUSEGENDERCODE = 2 then 'Female' else 'Any' end as SPOUSEGENDER,
    RELATIONSHIPTYPECODE.DESCRIPTION
from dbo.MARRIAGERELATIONSHIP
inner join dbo.RELATIONSHIPTYPECODE
    on RELATIONSHIPTYPECODE.ID = MARRIAGERELATIONSHIP.SPOUSERELATIONSHIPTYPECODEID
order by RELATIONSHIPTYPECODE.DESCRIPTION