通用树形表查询SQL

-- =============================================
-- -- Description:    获取当前表所选分类ID下的所有下级分类ID
-- =============================================
ALTERPROC[dbo].[GetTreeTable]
(
    @IDint=null,                --要查询的ID，查询所有输入NULL
    @TableNamevarchar(100),    --要查询的表名
    @IDNamevarchar(200),        --主字段名
    @SuperIDNamevarchar(200)    --上级字段名
)

AS
BEGIN

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

    --Declare @Lev int
    DECLARE@StrSQLvarchar(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