Confused how this query works

Question

I'm trying to learn advanced sql and how to use system queries (sql server). The below query is a little confusing.

CREATE PROC dbo.ShowHierarchy
(
    @Root int
)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @EmpID int, @EmpName varchar(30)

    SET @EmpName = (SELECT EmpName FROM dbo.Emp WHERE EmpID = @Root)
    PRINT REPLICATE('-', @@NESTLEVEL * 4) + @EmpName

    SET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE MgrID = @Root)

    WHILE @EmpID IS NOT NULL
    BEGIN
     EXEC dbo.ShowHierarchy @EmpID
     SET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE MgrID = @Root AND EmpID > @EmpID)
    END
END
GO

Taken from here:

http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm

Every time the query runs, how is the @EmpId parameter incremented? Does it do itself? Also, does the @root increment on every recursion? E.g. CEO is root, go to immediate subordinate, that immediate subordinate is now @root, etc.

Thanks

Answer 1

Every time the query runs, how is the @EmpId parameter incremented? Does it do itself?

This one:

SET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE MgrID = @Root AND EmpID > @EmpID)

selects minimal EmpID that is greater than the one already selected, and assigns it to the @EmpID.

Of course, if there are any, new @EmpID will be greater than the old one, which means that @EmpID is incremented.

Answer 2

In the last select @EmpID is set to the lowest EmpID that is larger than @EmpID. This way, @EmpID is "incremented" until there is no larger EmpID. In that case, the select min(EmpID) returns a null and the while-loop ends.

@Root is changed through the recursive call to ShowHierarchy in the EXEC-Statement. Basically, the current @EmpID becomes the new @Root in the execution that is triggered.

Answer 3

As Quassnoi says, this is where @EmpId get's incremented:

SET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE MgrID = @Root AND EmpID > @EmpID)

And yes, when the procedure is called recursively, it passes the @EmpID for the current recursion as the new @Root:

EXEC dbo.ShowHierarchy @EmpID