I created a Table-Valued Function to return a record to use within the
recursive processing of a CTE. It is giving me a compile error of

Msg 102, Level 15, State 1, Procedure upGetSubordinates, Line 91
Incorrect syntax near '.'.

Msg 102, Level 15, State 1, Procedure upGetSubordinates, Line 106
Incorrect syntax near '.'.

Below is the CTE Code:


create procedure staff.upGetSubordinates (@ii_MemberID      int
                                         ,@iti_Scale        tinyint
                                         ,@ivc_Center       varchar(10)
                                         ,@idt_AsOfDate     datetime) as


  set nocount on

  select @idt_AsOfDate = convert(varchar(10), isnull(@idt_AsOfDate,
getdate()), 102)


    with cteHierarchy (MemberID, LeaderID, FirstName, MiddleInitial,
LastName, Email, Title, Center, level) as
    (
      select m.MemberID, h.LeaderID, m.FirstName, m.MiddleInitial,
m.LastName, m.Email, d.Title, d.Center, 0 as level
        from Staff.Hierarchy h with (nolock) inner join Staff.Member m with
(nolock)
                                                     on m.MemberID =
h.MemberID
                                                    and m.IsDeleted = 0
                                            cross apply
Staff.ufGetDemographic(m.MemberID, @idt_AsOfDate) as d
--                                                     on d.MemberID =
m.MemberID
--                                             inner join Staff.Demographic
d with (nolock)
--                                                     on d.MemberID =
m.MemberID
--                                                    and @idt_AsOfDate
between d.StartDate and isnull(d.EndDate, '1-1-2200')
--                                                    and d.BusinessUnit =
'AELN'
--                                                    and d.IsDeleted = 0
       where @idt_AsOfDate between h.StartDate and isnull(h.EndDate,
'1-1-2200')
         and h.IsDeleted = 0
         and isnull(h.LeaderID, 0) = isnull(@ii_MemberID, 0)
       union all
      select m.MemberID, h.LeaderID, m.FirstName, m.MiddleInitial,
m.LastName, m.Email, d.Title, d.Center, 0 as level
        from Staff.Hierarchy h with (nolock) inner join Staff.Member m with
(nolock)
                                                     on m.MemberID =
h.MemberID
                                                    and m.IsDeleted = 0
                                            cross apply
Staff.ufGetDemographic(m.MemberID, @idt_AsOfDate) d
--                                                     on d.MemberID =
m.MemberID
--                                             inner join Staff.Demographic
d with (nolock)
--                                                     on d.MemberID =
m.MemberID
--                                                    and @idt_AsOfDate
between d.StartDate and isnull(d.EndDate, '1-1-2200')
--                                                    and d.BusinessUnit =
'AELN'
--                                                    and d.IsDeleted = 0
                                             inner join cteHierarchy c
                                                     on h.LeaderID =
c.MemberID
       where @idt_AsOfDate between h.StartDate and isnull(h.EndDate,
'1-1-2200')
         and h.IsDeleted = 0
     )

    select MemberID
          ,FirstName
          ,MiddleInitial
          ,LastName
          ,Email
          ,t.Display as 'Title'
          ,c.Display as 'Center'
          ,level
      from cteHierarchy h inner join Portal.Code t with (nolock)
                                  on t.Code = h.Title
                                 and t.Class = 'JOB'
                                          and @idt_AsOfDate between t.StartDate and
isnull(t.EndDate, '1-1-2200')
                                 and t.IsDeleted = 0
                                 and t.Scale = case when @iti_Scale is null
then t.Scale
                                                                         else @iti_Scale
                                                              end
                          inner join Portal.Code c with (nolock)
                                  on c.Code = h.Center
                                 and c.Class = 'CENTER'
                                          and @idt_AsOfDate between c.StartDate and
isnull(c.EndDate, '1-1-2200')
                                 and c.IsDeleted = 0
                                 and c.Code = case when @ivc_Center is null
then c.Code
                                                   else @ivc_Center
                                              end        


*********************************************************

below is my function code:

create function staff.ufGetDemographic (@ii_MemberID     int
                                       ,@idt_AsOfDate    datetime)
returns @itb_Demographic table (DemographicID int
                                 ,MemberID int
                                 ,HireDate datetime
                                 ,TermDate datetime
                                 ,AELNDate datetime
                                 ,BusinessUnit varchar(10)
                                 ,IsContractor bit
                                 ,IsHighPotential bit
                                 ,IsHighRisk bit
                                 ,Status varchar(10)
                                 ,Title varchar(10)
                                 ,Band varchar(10)
                                 ,Center varchar(10)
                                 ,Cubicle varchar(15)
                                 ,StartDate datetime
                                 ,EndDate datetime
                                 ,UpdatedBy varchar(128)
                                 ,UpdatedOn datetime
                                 ,IsDeleted bit)
as
begin


  insert into @itb_Demographic (DemographicID, MemberID, HireDate, TermDate,
                                AELNDate, BusinessUnit, IsContractor,
                                IsHighPotential, IsHighRisk, Status, Title,
Band,
                                Center, Cubicle, StartDate, EndDate,
UpdatedBy,
                                UpdatedOn, IsDeleted)
  select d.DemographicID
        ,d.MemberID
        ,d.HireDate
        ,d.TermDate
        ,d.AELNDate
        ,d.BusinessUnit
        ,d.IsContractor
        ,d.IsHighPotential
        ,d.IsHighRisk
        ,d.Status
        ,d.Title
        ,d.Band
        ,d.Center
        ,d.Cubicle
        ,d.StartDate
        ,d.EndDate
        ,d.UpdatedBy
        ,d.UpdatedOn
        ,d.IsDeleted
    from Staff.Demographic d with (nolock)
   where d.MemberID = @ii_MemberID
     and @idt_AsOfDate between convert(varchar(10), d.StartDate, 102) and
convert(varchar(10), isnull(d.EndDate, '1-1-2200'), 102)
     and d.IsDeleted = 0
     and d.StartDate = (select max(d1.StartDate)
                          from Staff.Demographic d1 with (nolock)
                         where d1.MemberID = d.MemberID
                           and @idt_AsOfDate between convert(varchar(10),
d1.StartDate, 102) and convert(varchar(10), isnull(d1.EndDate, '1-1-2200'),
102)
                           and d1.IsDeleted = 0)

  return
end

Using ranking functions in a subquery to feed a CLR function values in a specified order.
Challenging Query - Please Help
Convert *= / =* to outer joins (ANSI compliant)
SQL Include Empty Months
create a sp from query output