问使用表变量作为中间结果的table递归？

EN

-- Sample data.
declare @RecursiveTable as Table ( AId Int, BId Int );
insert into @RecursiveTable ( AId, BId ) values
  ( 1, 2 ), ( 1, 3 ), ( 1, 4 ),
  ( 5, 1 ),
  ( 2, 6 ), ( 2, 7 ),
  ( 3, 8 ), ( 3, 9 ),
  ( 4, 10 );
select * from @RecursiveTable;

-- Walk the tree with a recursive CTE.
--   NB: This is woefully inefficient since we cannot promptly detect
--   rows that have already been processed.
declare @Start as Int = 5;
with Pairs as (
  select AId, BId, Cast( AId as VarChar(10) ) + '/' + Cast( BId as VarChar(10) ) as Pair
    from @RecursiveTable ),
 Relations as (
  select AId, BId, Cast( '|' + Pair + '|' as VarChar(1024) ) as Path
    from Pairs
    where AId = @Start or BId = @Start
  union all
  select P.AId, P.BId, Cast( R.Path + P.Pair + '|' as VarChar(1024) )
    from Relations as R inner join
      Pairs as P on P.BId = R.AId or P.AId = R.BId or
        P.BId = R.BId or P.AId = R.AId
    where CharIndex( '|' + P.Pair + '|', R.Path ) = 0
  )
  -- To see how terrible this is, try: select * from Relations
  select AId as Id
    from Relations
    where AId <> @Start
  union
  select BId
    from Relations
    where BId <> @Start
    order by Id;

-- Try again using a loop to add relations to a working table.
declare @Relations as Table ( AId Int, BId Int );
insert into @Relations
  select AId, BId
    from @RecursiveTable
    where AId = @Start or BId = @Start;
while @@RowCount > 0
  insert into @Relations
    select RT.AId, RT.BId
      from @Relations as R inner join
        @RecursiveTable as RT on RT.BId = R.BId or RT.AId = R.AId or
          RT.BId = R.AId or RT.AId = R.BId
    except
    select AId, BId
      from @Relations;
select AId as Id
  from @Relations
  where AId <> @Start
union
  select BId
  from @Relations
  where BId <> @Start
  order by Id;

CREATE FUNCTION fSynonyms(@val Int, @maxRecursions Int)
  RETURNS @ret TABLE
  (
     num    Int
  )
AS BEGIN 
  DECLARE @Values TABLE ( num Int )
  INSERT INTO @Values VALUES(@val)
  DECLARE @i Int = 0

  WHILE @i < @maxRecursions
  BEGIN
     INSERT INTO VAL
       SELECT aid 
       FROM @Values VAL
            INNER JOIN Aliases ALIAS
               ON ALIAS.bid = VAL.num

     INSERT INTO VAL
        SELECT bid
         FROM @Values VAL
              INNER JOIN Aliases ALIAS
                ON ALIAS.aid = VAL.num

     SET @i += 1
   END

   INSERT INTO @Ret
      SELECT DISTINCT num FROM @Values

    RETURN
END