表结构：
演示数据如下：
写法一：
CREATE PROCEDURE UP_S_LocationTreeByLocationID
@LocationID VARCHAR(36)
AS
BEGIN
SET NOCOUNT ON
DECLARE @Level INT
DECLARE @Temp TABLE(LocationID VARCHAR(36),ParentID VARCHAR(36),iLevel INT,LocationNm VARCHAR(40))
DECLARE @Stack TABLE(LocationID VARCHAR(36),iLevel INT)
SET @Level = 1
INSERT INTO @Temp
SELECT LocationID,
ParentLocationID,
@Level,
LocationNm
FROM tblLocation
WHERE LocationID = @LocationID
INSERT INTO @Stack VALUES(@LocationID,@Level)
WHILE @Level > 0
BEGIN
IF EXISTS(SELECT 1 FROM @Stack WHERE iLevel = @Level)
BEGIN
SELECT @LocationID = LocationID
FROM @Stack
WHERE iLevel = @Level
PRINT @LocationID
DELETE FROM @Stack
WHERE LocationID = @LocationID
AND iLevel = @Level
INSERT INTO @Stack
SELECT LocationID,@Level+1
FROM tblLocation
WHERE ParentLocationID = @LocationID
INSERT INTO @Temp
SELECT LocationID,
ParentLocationID,
@Level+1,
LocationNm
FROM tblLocation
WHERE ParentLocationID = @LocationID
IF @@ROWCOUNT > 0
BEGIN
SET @Level = @Level +1
END
END
ELSE
SET @Level = @Level -1
END
SELECT * FROM @Temp
END
GO
写法二：
CREATE PROCEDURE UP_S_LocationTreeByLocationID
@LocationID VARCHAR(36)
AS
BEGIN
SET NOCOUNT ON
DECLARE @Level INT
SET @Level = 1
DECLARE @Temp TABLE(LocationID VARCHAR(36),ParentID VARCHAR(36),iLevel INT,LocationNm VARCHAR(40),RouteID VARCHAR(36),RouteNm NVARCHAR(40))
INSERT INTO @Temp
SELECT LocationID,
ParentLocationID,
@Level,
LocationNm
FROM tblLocation
WHERE LocationID = @LocationID
WHILE EXISTS(SELECT 1 FROM tblLocation a,@Temp b WHERE a.ParentLocationID = b.LocationID AND a.LocationID NOT IN(SELECT LocationID FROM @Temp))
BEGIN
INSERT INTO @Temp
SELECT LocationID,
ParentLocationID,
@Level+1,
LocationNm,
RouteID,
RouteNm
FROM tblLocation JOIN @Temp t ON ParentLocationID = LocationID
AND LocationID NOT IN(SELECT LocationID FROM @Temp)
SET @Level = @Level +1
END
SELECT * FROM @Temp
END
GO
执行结果如下：
执行语句：UP_S_LocationTreeByLocationID ‘1′
第一种写法
第二种写法
No Comments
Be the first to comment on this entry.
Leave a comment
Fields in bold are required. Email addresses are never published or distributed.
Some HTML code is allowed:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>URLs must be fully qualified (eg: http://www.dbifan.com),and all tags must be properly closed.
Line breaks and paragraphs are automatically converted.
Please keep comments relevant. Off-topic, offensive or inappropriate comments may be edited or removed.