SQL 递归树形结构有关问题_Sql Server

SQL 递归树形结构问题
WITH subqry(id, name, pid,Levels)
     AS (SELECT ID,
                name,
                ParentId,Levels
         FROM   StockLocation
         WHERE  ParentId = 44   AND ID = 49
         UNION ALL
         SELECT b.ID,
                b.name,
                ParentId,
                b.Levels
         FROM   StockLocation b,
                subqry C
         WHERE  b.id = C.pid)
SELECT *
FROM   subqry d

本来只有4级但是现在查出来是这个结果怎么去判断Levels等级也加上去啊
------解决思路----------------------

;WITH subqry(id, name, pid,Levels)
     AS (SELECT ID,
                name,
                ParentId,Levels
         FROM   StockLocation AS a
         WHERE  NOT EXISTS(SELECT 1 FROM StockLocation WHERE ParentId=a.ID)
         UNION ALL
         SELECT b.ID,
                b.name,
                ParentId,
                b.Levels
         FROM   StockLocation b,
                subqry C
         WHERE  b.id = C.pid )
SELECT *
FROM   subqry d

------解决思路----------------------
Levels=4--或条件改为

;WITH subqry(id, name, pid,Levels)
     AS (SELECT ID,
                name,
                ParentId,Levels
         FROM   StockLocation AS a
         WHERE  Levels=4
         UNION ALL
         SELECT b.ID,
                b.name,
                ParentId,
                b.Levels
         FROM   StockLocation b,
                subqry C
         WHERE  b.id = C.pid )
SELECT *
FROM   subqry d