set nocount on

-- ***************************************************
-- ** START: Set up tables and fill with test data
-- ***************************************************
create table #Members (
   MemberID int identity(1,1) not null primary key clustered
   , Member_Name varchar(20) null
)

create table #Addresses (
   AddressID int identity(1,1) not null primary key clustered
   , MemberID int not null
   , Address varchar(100) not null
)

create table #Emails (
   EmailID int identity(1,1) not null primary key clustered
   , MemberID int not null
   , Email varchar(100) not null
)

insert #Members (Member_Name) Values ('Joe User')

insert #Addresses (MemberID , Address) Values ( 1 , '123 Main Street')
insert #Addresses (MemberID , Address) Values ( 1 , '333 Broadway Road')
insert #Addresses (MemberID , Address) Values ( 1 , '9332 Centre Street')

insert #Emails (MemberID , Email) Values ( 1 , 'joe@eee.com')
insert #Emails (MemberID , Email) Values ( 1 , 'joe@3ewseee.com')
insert #Emails (MemberID , Email) Values ( 1 , 'juser@ppoppo.com')
insert #Emails (MemberID , Email) Values ( 1 , 'joe.user@abbdn.com')
-- ***************************************************
-- ** END  : Set up tables and fill with test data
-- ***************************************************
go

-- ***************************************************
-- ** START: Create SP to return data
-- ***************************************************
create procedure #GetMemberData
	@MemberID int
AS

set nocount on

create table #tempEmails (
   RowId int identity(1,1) not null primary key clustered
   , EmailID int not null
)

create table #tempAddresses (
   RowId int identity(1,1) not null primary key clustered
   , AddressID int not null
)

Insert #tempEmails (EmailID) 
   Select EmailID from #Emails where MemberID = @MemberID Order by EmailID

Insert #tempAddresses (AddressID) 
   Select AddressID from #Addresses where MemberID = @MemberID Order by AddressID

select @MemberID As MemberID , e.EmailID , a.AddressID
from #tempEmails e
full outer join #tempAddresses a
on e.RowID = a.RowID

drop table #tempEmails, #tempAddresses

return 0
go
-- ***************************************************
-- ** END  : Create SP to return data
-- ***************************************************


-- Call the SP and get the data
exec #GetMemberData @MemberID = 1

-- drop temp tables and procs
drop table #Members , #Addresses , #Emails
drop procedure #GetMemberData

-- here is the output:
MemberID    EmailID     AddressID   
----------- ----------- ----------- 
1           1           1
1           2           2
1           3           3
1           4           NULL