Inside Recursive CTEs

-- setup a simple table to query ********************
declare @t table (
	Id int,
	ParentId int	
)
insert @t(Id, ParentId)
values (1, 0), (2, 1), (3, 1), (4, 2), (5, 4), (6, 3)



-- the recursive query ******************************
;with cte as (
	select	1 id, 0 ParentId
	union all
	select	C.Id, C.ParentId
	from	@t as C
			inner join cte P on C.ParentId = P.Id	
)
select * from cte


-- doing the same thing by hand *********************
declare @cte table (
	id int,
	ParentId int,
	Pass int
)
declare @Pass int
set		@Pass = 0

insert	@cte(id, ParentId, Pass)
values	(1, 0, @Pass)

while	 @@ROWCOUNT > 0
begin
	-- increment pass
	set @Pass = @Pass + 1
	
	-- insert next set
	insert	@cte (id, ParentId, Pass)
	select	C.Id, C.ParentId, @Pass
	from	@t C
			inner join @cte P 
				on C.ParentId = P.id
				and P.Pass = @Pass - 1 -- only look at rows from the previous pass
end	
select * from @cte


-- simulating the pass in the CTE *********************
;with cte as (
	select	1 id, 0 ParentId, 0 Pass -- start with Pass = 0
	union all
	select	C.Id, 
			C.ParentId, 
			P.Pass + 1	-- Increment Pass column with each pass by adding 1 to the previous value (note that *P*.Pass was used to add to)
	from	@t as C
			inner join cte P on C.ParentId = P.Id	
)
select * from cte