USP_DATALIST_GETGROUPBYREGISTRANT

CREATE procedure dbo.USP_DATALIST_GETGROUPBYREGISTRANT
  (
        @REGISTRANTID uniqueidentifier
  )
  as
      set nocount on;

    declare @groupId uniqueidentifier = null,
            @parentGroupId uniqueidentifier = null,
            @childGroupId uniqueidentifier = null

select @groupId = TM.TEAMFUNDRAISINGTEAMID  
  from REGISTRANT RG
 inner join EVENT EV on RG.EVENTID = EV.ID 
 inner join TEAMFUNDRAISER TF on RG.CONSTITUENTID = TF.CONSTITUENTID and EV.APPEALID = TF.APPEALID 
 inner join TEAMFUNDRAISINGTEAMMEMBER TM on TF.ID = TM.TEAMFUNDRAISERID 
 where RG.ID = @REGISTRANTID

if (@groupId is not null)
begin
  select @parentGroupId = T.PARENTTEAMID
    from TEAMFUNDRAISINGTEAM T
   where T.ID = @groupId
  if (@parentGroupId is not null)
  begin
    set @childGroupId = @groupId
    set @groupId = @parentGroupId
  end
end

select GROUPID = @groupId, CHILDGROUPID = @childGroupId