USP_DATALIST_INSTANCEGUIDFORMEGEDATA

CREATE procedure dbo.USP_DATALIST_INSTANCEGUIDFORMEGEDATA(
    @EVENTID uniqueidentifier,
    @CONSTITUENTID uniqueidentifier=null,
    @REGISTRANTID uniqueidentifier=null
)
as
    set nocount on;

    SELECT 
    C.ID AS CONSTITUENTID 
    ,RG.ID AS REGISTRANTID
    ,TFT.ID AS CHILDGROUPID
    ,(case when TFT.PARENTTEAMID IS NULL THEN TFT.ID ELSE TFT.PARENTTEAMID END) AS GROUPID
    from dbo.REGISTRANT RG    
    inner join [EVENT] EV ON EV.ID = RG.EVENTID  
    inner  join dbo.CONSTITUENT C ON RG.CONSTITUENTID = C.ID
    left outer join dbo.TEAMFUNDRAISER TF on TF.CONSTITUENTID=C.ID AND TF.APPEALID=EV.APPEALID 
    left outer join dbo.TEAMFUNDRAISINGTEAMMEMBER TFTM ON TFTM.TEAMFUNDRAISERID =TF.ID 
    left outer join dbo.TEAMFUNDRAISINGTEAM TFT ON TFT.ID=TFTM.TEAMFUNDRAISINGTEAMID 
    left outer join dbo.TEAMFUNDRAISINGTEAM PTFT ON PTFT.ID=TFT.PARENTTEAMID  
    WHERE (C.ID=@CONSTITUENTID or @CONSTITUENTID is null)
    AND EV.ID=@EVENTID
    AND (RG.ID=@REGISTRANTID or @REGISTRANTID is null)