>create procedure dba.sel_contacts( > in @namePK integer) > >begin > > select > number(*) as sqn > into #seqenceNo > from names na > key left join address ad > key left join phonenumber ph > key left join email > where na.namepk = @namePK ; > > select > ad.addresspk, > ad.xlinkfk, > ad.typefk, > ad.descrp, > ad.address1, > ad.address2, > ad.address3, > ad.city, > ad.state, > ad.zipcode, > ad.country, > ad.namefk, > number(*) as sqn > into #add > from address ad > where ad.namefk = @namePK > and ad.delflag = 0 ; > > select > ph.phonenumberpk, > ph.xlinkfk, > ph.typefk, > ph.descrp, > ph.prefix, > ph.phonenumber, > ph.extension, > ph.delflag, > ph.namefk, > number(*) as sqn > into #phn > from phonenumber ph > where ph.namefk = @namePK > and ph.delflag = 0 ; > > select > em.emailaddresspk, > em.xlinkfk, > em.typefk, > em.descrp, > em.email, > em.namefk, > number(*) as sqn > into #emm > from email em > where em.namefk = @namePK > and em.delflag = 0 ; > > select * from > ( select > na.namepk, > na.loginuser, > na.typefk, > na.firstname, > na.lastname, > na.title, > na.prefix, > na.ownerfk, > sq.sqn > from names na > cross join #seqenceNo sq > where na.namePK = @namePK) as naa > left join ( > select * from #add > ) as ad > on ad.sqn = naa.sqn > left join ( > select * from #phn > ) phh > on phh.sqn = naa.sqn > left join ( > select * from #emm > ) as emm > on emm.sqn = naa.sqn > WHERE COALESCE(ad.NameFK, emm.NameFK, phh.NameFK) IS NOT NULL ; >end; > >>