stack-based traversing

/* this is my stack-based approach to traversing, sql server has limit on nesting depth of stored proc to 32, it's not viable to use recursive algo */


alter PROCEDURE DrillDownlinesTree
@RootSponsorToDrill VARCHAR(10)

AS

DECLARE @SponsorToDrill VARCHAR(10)
SET @SponsorToDrill = @RootSponsorToDrill

DECLARE @Drills TABLE
(
DistNo VARCHAR(10),
Sponsor VARCHAR(10),
TreeLevel INT,
DownlineCount INT,
ImmediateDownlineCount INT,
RecruitProximity INT
)


INSERT INTO @Drills(
DistNo,
Sponsor,
TreeLevel,
DownlineCount,
ImmediateDownlineCount,
RecruitProximity)
SELECT DistNo, Sponsor, TreeLevel, DownlineCount, ImmediateDownlineCount, 0 FROM Recruit WHERE DistNo = @RootSponsorToDrill



SET NOCOUNT ON

DECLARE @StackDist TABLE
(
StackDepth INT,
Ndx INT,
DistNo VARCHAR(10),
ImmediateDownlineCount INT
)

DECLARE @StackCounter TABLE
(
StackDepth INT,
I INT,
N INT
)



DECLARE @StackDepth INT
SET @StackDepth = 1

goDrill:

--- push all dist of sponsor, stackdepth..
DECLARE @N INT

SET @N = 0
DECLARE @c CURSOR
SET @C = CURSOR FOR
SELECT DistNo, DownlineCount FROM Recruit WHERE Sponsor = @SponsorToDrill
OPEN @C

DECLARE @DistNo VARCHAR(10)
DECLARE @ImmediateDownlineCount INT

FETCH NEXT FROM @C INTO @DistNo, @ImmediateDownlineCount
WHILE @@FETCH_STATUS = 0 BEGIN


INSERT INTO @StackDist(StackDepth, Ndx, DistNo, ImmediateDownlineCount)
SELECT @StackDepth, @N, @DistNo, @ImmediateDownlineCount
SET @N = @N + 1
FETCH NEXT FROM @C INTO @DistNo, @ImmediateDownlineCount
END
CLOSE @C
DEALLOCATE @C
-- ..push all dist of sponsor, stackdepth

DECLARE @I INT

SET @I = 0
INSERT INTO @Drills(
DistNo,
Sponsor,
TreeLevel,
DownlineCount,
ImmediateDownlineCount,
RecruitProximity)
SELECT DistNo, Sponsor, TreeLevel, DownlineCount, ImmediateDownlineCount, @StackDepth FROM Recruit WHERE Sponsor = @SponsorToDrill

goProcess:
WHILE @I < @N BEGIN


declare @s varchar(800)
select @s = REPLICATE('-',@StackDepth * 2) + DistNo FROM @StackDist WHERE StackDepth = @StackDepth AND Ndx = @I
print @s


IF (SELECT ImmediateDownlineCount FROM @StackDist WHERE StackDepth = @StackDepth AND Ndx = @I) > 0 BEGIN
-- push i, n, stackdepth..
INSERT INTO @StackCounter(StackDepth, I, N) SELECT @StackDepth, @I, @N

SELECT @SponsorToDrill = DistNo FROM @StackDist WHERE StackDepth = @StackDepth AND Ndx = @I

SET @StackDepth = @StackDepth + 1

GOTO goDrill
-- ..push i, n, stackdepth
END
SET @I = @I + 1
END


IF @StackDepth > 1 BEGIN
DELETE FROM @StackDist WHERE StackDepth = @StackDepth


DELETE FROM @StackCounter WHERE StackDepth = @StackDepth
SET @StackDepth = @StackDepth - 1
-- pop I, N..
SELECT @I = I + 1, @N = N FROM @StackCounter WHERE StackDepth = @StackDepth

-- ..pop I, N

GOTO goProcess
END

SET NOCOUNT OFF





--SELECT * FROM @Drills order by sponsor