USP_DATALIST_TEAMFUNDRAISINGTEAMWITHCAPTAINS

                CREATE procedure dbo.USP_DATALIST_TEAMFUNDRAISINGTEAMWITHCAPTAINS
                (
                    @APPEALIDPARENTTEAMID nvarchar(72) = null
                )
                as
                    set nocount on;

                    declare @APPEALID uniqueidentifier;
                    set @APPEALID = left(@APPEALIDPARENTTEAMID, 36);

                    declare @PARENTTEAMID uniqueidentifier;
                    if len(@APPEALIDPARENTTEAMID) = 72
                        set @PARENTTEAMID = right(@APPEALIDPARENTTEAMID, 36);

                    select
                        TEAMFUNDRAISINGTEAM.ID,
                        TEAMFUNDRAISINGTEAM.NAME,
                        TEAMFUNDRAISINGTEAM.GOAL,
                        cast
                            (
                                case
                                    when
                                        (
                                            select top 1
                                                CHILDTEAM.ID
                                            from
                                                dbo.TEAMFUNDRAISINGTEAM as CHILDTEAM
                                            where
                                                CHILDTEAM.PARENTTEAMID = TEAMFUNDRAISINGTEAM.ID
                                        ) is not null
                                        or
                                        (
                                            select top 1
                                                TEAMMEMBER.ID
                                            from
                                                dbo.TEAMFUNDRAISINGTEAMMEMBER as TEAMMEMBER
                                            where
                                                TEAMMEMBER.TEAMFUNDRAISINGTEAMID = TEAMFUNDRAISINGTEAM.ID
                                        ) is not null
                                    then
                                        1
                                    else
                                        0
                                end
                                as bit
                            ) as [HASCHILDREN],
                        TEAMFUNDRAISINGTEAMCAPTAIN.ID,
                        TEAMFUNDRAISINGTEAMCAPTAIN.CONSTITUENTID,
                        NF.NAME [CAPTAINNAME],
                        APPEAL.BASECURRENCYID
                    from
                        dbo.TEAMFUNDRAISINGTEAM
                        left join dbo.TEAMFUNDRAISINGTEAMCAPTAIN on TEAMFUNDRAISINGTEAM.ID = TEAMFUNDRAISINGTEAMCAPTAIN.TEAMFUNDRAISINGTEAMID
                        left join dbo.APPEAL on APPEAL.ID = TEAMFUNDRAISINGTEAM.APPEALID
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(TEAMFUNDRAISINGTEAMCAPTAIN.CONSTITUENTID) NF
                    where
                        TEAMFUNDRAISINGTEAM.APPEALID = @APPEALID
                        and
                        (
                            TEAMFUNDRAISINGTEAM.PARENTTEAMID = @PARENTTEAMID
                            or
                            (
                                TEAMFUNDRAISINGTEAM.PARENTTEAMID is null
                                and
                                @PARENTTEAMID is null
                            )
                        )
                    order by
                        TEAMFUNDRAISINGTEAM.NAME,
                        CAPTAINNAME;