通用树形表查询SQL

-- =============================================
-- Author:        雷磊
-- Create date: 2008-7-23
-- Description:    获取当前表所选分类ID下的所有下级分类ID
-- =============================================
ALTER PROC [dbo].[GetTreeTable] 
(
    @ID int= null,                --要查询的ID，查询所有输入NULL
    @TableName varchar(100),    --要查询的表名
    @IDName varchar(200),        --主字段名
    @SuperIDName varchar(200)    --上级字段名
)

AS
BEGIN

    --create table #Tab (ID int, SuperID int, Lev int)

    --Declare @Lev int
    DECLARE @StrSQL varchar(5000)
--    Set @Lev=0
--
--    While @Lev=0 or @@ROWCount>0
--    Begin
--        Set @Lev=@Lev+1
--        SET @StrSQL = 'Insert #Tab(ID, SuperID, Lev) Select '+@IDName+', '+@SuperIDName+', '+convert(varchar(10),@Lev)+' From '+@TableName+' Where ('+convert(varchar(10),@Lev)+'=1 and (('+@IDName+'='+convert(varchar(10),@ID)+') or (('+convert(varchar(10),@ID)+' is null or '+convert(varchar(10),@ID)+'='''') and '+@SuperIDName+' is null))) or ('+@SuperIDName+' in (Select ID From #Tab Where Lev='+convert(varchar(10),@Lev)+'-1)) order by '+@IDName
--        --print @StrSQL
--        EXEC (@StrSQL)
--    End
    SET @StrSQL = '
    WITH DirectReports('+@IDName+', '+@SuperIDName+',Level) AS 
    (
        SELECT '+@IDName+', '+@SuperIDName+', 0 AS Level
        FROM '+@TableName+'
        WHERE ('+@IDName+'='+CONVERT(VARCHAR(100),ISNULL(@ID,''))+') OR ('+@SuperIDName+' IS NULL AND '+CONVERT(VARCHAR(100),ISNULL(@ID,''))+' = 0)
        UNION ALL
        SELECT e.'+@IDName+', e.'+@SuperIDName+', Level + 1
        FROM '+@TableName+' e
        INNER JOIN DirectReports d
        ON e.'+@SuperIDName+' = d.'+@IDName+' 
    )
    
    SELECT '+@IDName+','+@SuperIDName+',Level
    FROM DirectReports 
    ORDER BY Level
    '
    EXEC (@StrSQL)
    PRINT @StrSQL
    --select * from #Tab
    --drop table #Tab
END



-- exec [GetTreeTable]    1060,'T_Department','DepID','SuperDepID'

-- exec [GetTreeTable]    NULL,'T_WLFLDY','WLFLID','SJFL'

--SELECT * FROM T_WLFLDY