树形结构查询过程

USE [Northwind]
GO
/****** 对象:  StoredProcedure [dbo].[USP_TREEVIEW]    脚本日期: 12/18/2009 07:59:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER  PROCEDURE [dbo].[USP_TREEVIEW]
      @TABLE NVARCHAR(50)         --表名
     ,@PIDCOL NVARCHAR(50)      --父结点标识字段名
     ,@IDCOL NVARCHAR(50)         --子结点标识字段名
     ,@DESCCOL NVARCHAR(50)  --显示节点标签字段
AS
BEGIN
DECLARE @SQL VARCHAR(8000)

SELECT @SQL='
DECLARE @TMP TABLE(
    [PID_NUM] [INT]  NULL,
    [ID_NUM] [int] IDENTITY (1, 1) NOT NULL ,
    ['+@PIDCOL+'] [nvarchar] (20)  NULL ,
    ['+@IDCOL+'] [nvarchar] (20)  NOT NULL ,
    ['+@DESCCOL+'] [ntext]  NULL,
    PATH NVARCHAR(1000) NULL,
    NEXT_PARENTID INT NULL,
     PRE_PARENTID INT NULL,
    PATHCHAR NVARCHAR(1000) DEFAULT('''') NULL,
                ISFIRST BIT DEFAULT(1) NULL
)   
INSERT  @TMP (['+@PIDCOL+'],['+@IDCOL+'],['+@DESCCOL+'])
SELECT ['+@PIDCOL+'],['+@IDCOL+'],['+@DESCCOL+'] FROM '+@TABLE+' order by '+@PIDCOL+','+@IDCOL+'

UPDATE A SET PID_NUM=B.ID_NUM,PATH=CAST(A.ID_NUM AS NVARCHAR),NEXT_PARENTID=B.ID_NUM
FROM @TMP A LEFT JOIN @TMP B ON A.'+@PIDCOL+'=B.'+@IDCOL+'

WHILE @@ROWCOUNT!=0
UPDATE A SET
A.PATH=ISNULL(CAST(A.NEXT_PARENTID AS NVARCHAR)+''.'','''')+A.PATH
,A.PATHCHAR=CASE WHEN A.ISFIRST=1 THEN
                 CASE WHEN  not exists (select * from @TMP where PID_NUM=A.NEXT_PARENTID and ID_NUM>A.ID_NUM ) THEN
            ''└'' + replicate(''─'', 2) + A.PATHCHAR
                 ELSE
            ''├'' +replicate(''─'', 2) + A.PATHCHAR
                 END
             ELSE
                   CASE WHEN  exists (select * from @TMP where PID_NUM=A.NEXT_PARENTID and ID_NUM>( select ID_NUM from @TMP where ID_NUM=A.PRE_PARENTID) ) THEN
                           ''│'' + replicate(''　'', 2) +A.PATHCHAR
                   ELSE
                          replicate(''　'', 3) +A.PATHCHAR
                   END
                         END
,A.PRE_PARENTID=A.NEXT_PARENTID
,A.NEXT_PARENTID=B.PID_NUM
,A.ISFIRST=0
FROM @TMP A LEFT JOIN @TMP B ON A.NEXT_PARENTID=B.ID_NUM
WHERE A.NEXT_PARENTID IS NOT NULL
--SELECT * FROM @TMP ORDER BY PATH
SELECT PATHCHAR+CAST(['+@DESCCOL+'] AS NVARCHAR) AS '+'[Label_'+@DESCCOL+'],['+@IDCOL+'],['+@PIDCOL+']
FROM @TMP
ORDER BY PATH
'
--print (@SQL)
exec( @SQL)
END
EXEC USP_TREEVIEW 'Northwind..Employees','ReportsTo','EmployeeID','LastName'