USP_DATALIST_PROMOTEDEMOTEMEMBERLIST

CREATE procedure dbo.USP_DATALIST_PROMOTEDEMOTEMEMBERLIST
(  
    @CONSTITUENTID uniqueidentifier,  
    @EVENTID uniqueidentifier
)  
as  
set nocount on; 
SELECT 
    C.ID, 
    R.ID AS RID,
    C.NAME,
    CASE WHEN EXISTS(
            SELECT ID FROM TEAMFUNDRAISINGTEAMCAPTAIN TFTC (NOLOCK) 
            WHERE FNTEAM.TEAMID = TFTC.TEAMFUNDRAISINGTEAMID AND TFTC.CONSTITUENTID = C.ID)
        THEN 1 
        ELSE 0
    END 
    AS ISLEADER,
    TEAMTYPECODE
FROM dbo.UFN_FAF_GETTEAMINFO_BY_REGISTRANT(@CONSTITUENTID, @EVENTID) FNTEAM
INNER JOIN TEAMFUNDRAISINGTEAMMEMBER TFTM (NOLOCK) on FNTEAM.TEAMID = TFTM.TEAMFUNDRAISINGTEAMID
INNER JOIN dbo.TEAMFUNDRAISER TF ON TFTM.TEAMFUNDRAISERID = TF.ID
INNER JOIN dbo.CONSTITUENT C (NOLOCK) on TF.CONSTITUENTID = C.ID  
INNER JOIN dbo.REGISTRANT R (NOLOCK) on C.ID = R.CONSTITUENTID AND R.EVENTID = @EVENTID
WHERE C.ID <> @CONSTITUENTID