USP_BBNC_CONSTITSPOUSEDATA

            CREATE procedure dbo.USP_BBNC_CONSTITSPOUSEDATA(@ID int)
            as

                set NOCOUNT on;

                select 
                    TITLECODE.DESCRIPTION as TITLE,
                    TITLE2CODE.DESCRIPTION as TITLE2,
                    SPOUSE.FIRSTNAME,
                    SPOUSE.MIDDLENAME,
                    SPOUSE.KEYNAME,
                    SPOUSE.MAIDENNAME,
                    SUFFIXCODE.DESCRIPTION as SUFFIX,
                    SUFFIX2CODE.DESCRIPTION as SUFFIX2,
                    dbo.UFN_CONSTITUENT_BUILDNAME(SPOUSE.ID) as FULLNAME,
                    EDUCATIONALHISTORY.CLASSOF,
          SPOUSE.ID,
          RELATIONSHIP.RELATIONSHIPTYPECODEID,
          RELATIONSHIP.RECIPROCALTYPECODEID,
          -- SHL BBIS Bug 372118; Getting Spouse's gender code as well so that it is not overwritten with "Unknown" later on in batch

          SPOUSE.GENDERCODE
          -- if you add outputs to this make sure you also add them to NetCommunityConstituentSpouse.View.xml

                from
                    dbo.CONSTITUENT
                inner join
                    dbo.RELATIONSHIP on CONSTITUENT.ID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID
                inner join
                    dbo.CONSTITUENT as SPOUSE on RELATIONSHIP.RECIPROCALCONSTITUENTID = SPOUSE.ID
                left join
                    dbo.TITLECODE on SPOUSE.TITLECODEID = TITLECODE.ID
                left join
                    dbo.TITLECODE TITLE2CODE on SPOUSE.TITLE2CODEID = TITLE2CODE.ID
                left join
                    dbo.SUFFIXCODE on SPOUSE.SUFFIXCODEID = SUFFIXCODE.ID
                left join
                    dbo.SUFFIXCODE SUFFIX2CODE on SPOUSE.SUFFIX2CODEID = SUFFIX2CODE.ID
                left join
                    dbo.EDUCATIONALHISTORY on SPOUSE.ID = EDUCATIONALHISTORY.CONSTITUENTID and EDUCATIONALHISTORY.ISPRIMARYRECORD = 1
                where 
                    CONSTITUENT.SEQUENCEID = @ID and ISSPOUSE = 1;