SQL 树形菜单查询，查询本身和所有下级项

SQL 树形菜单查询，查询本身和所有下级项

 WITH Temp AS     (SELECT * FROM Sys_Organization WHERE OUGUID = @OUGUID AND OrgState = 1 AND IsAvailable = 1 AND IsDel = 0        UNION ALL        SELECT A.* FROM Sys_Organization A INNER JOIN Temp B ON A.OUGUID = B.ParentOUGUID AND A.OrgState = 1 AND A.IsAvailable = 1 AND A.IsDel = 0    ) SELECT DISTINCT * FROM Temp ORDER BY OULevel

函数

set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo-- =============================================-- Author:HZ-- Create date: 2011/10/17-- Description:传入字典类别，字典编码得到该级及以下类型的字典编码-- =============================================ALTER FUNCTION [dbo].[GetCodeValueDetail](@CodeFlag nvarchar(20),@CodeValue nvarchar(20))RETURNS @table table(CodeValue nvarchar(20))ASbegindeclare @IncludeItemCount intdeclare @ParentCode nvarchar(20)declare @Table_ParentID table(ParentCode nvarchar(20))declare @Table_IParentID table(IParentCode nvarchar(20))declare @Table_Mid table(ParentCode nvarchar(20))declare CursorCodeValue cursor forselect distinct CodeValue from dbo.Sys_CodeValueDetailwhere  CodeValue= @CodeValue and CodeFlag=@CodeFlagopen CursorCodeValuefetch next from CursorCodeValue into @ParentCodewhile @@fetch_status = 0begininsert into @Table_ParentID values (@ParentCode)insert into @Table_IParentID values (@ParentCode)select @IncludeItemCount = count(a.CodeValue) from Sys_CodeValueDetail a,@Table_IParentID b where a.ParentCode = b.IParentCodewhile (@IncludeItemCount > 0)begininsert into @Table_ParentIDselect distinct a.CodeValuefrom Sys_CodeValueDetail a , @Table_IParentID b where a.ParentCode = b.IParentCodedelete @Table_Midinsert into @Table_Midselect * from @Table_IParentIDdelete @Table_IParentIDinsert into @Table_IParentIDselect distinct a.CodeValuefrom Sys_CodeValueDetail a,@Table_Mid b where a.ParentCode = b.ParentCodeselect @IncludeItemCount = count(a.CodeValue)from Sys_CodeValueDetail a,@Table_IParentID b where a.ParentCode = b.IParentCodeenddelete @Table_IParentIDdelete @Table_Midfetch next from CursorCodeValue into @ParentCodeendclose CursorCodeValuedeallocate CursorCodeValueinsert into @Table_Mid select distinct * from @Table_ParentID insert into @table select * from @Table_Midreturnend