--得到子节点
CREATE Function GetRoleChild(@RoleID bigint, @Level int)
Returns @Child Table([RoleID] int ,[RoleName] varchar(64),[RoleFlag] varchar(64),[RoleParentsID] int,[RoleType] varchar(64))
As
Begin
set @level = @Level -1
Insert @Child Select * From [T_Role] Where [RoleParentsID]=@RoleID
While @@ROWCOUNT>0 and @level > 0
begin
set @level = @Level -1
Insert @Child Select B.* From @Child A Inner Join [T_Role] B On A.[RoleID]=B.[RoleParentsID] Where B.[RoleParentsID] Not In (Select Distinct [RoleParentsID] From @Child)
end
Return
End
--得到父节点
CREATE Function GetRoleParents(@RoleID bigint, @Level int)
Returns @Parents Table([RoleID] int ,[RoleName] varchar(64),[RoleFlag] varchar(64),[RoleParentsID] int,[RoleType] varchar(64))
As
Begin
set @level = @Level -1
Insert @Parents Select * From [T_Role] where [RoleID] = (Select [RoleParentsID] From [T_Role] Where [RoleID]=@RoleID)
While @@ROWCOUNT>0 and @level > 0
begin
set @level = @Level -1
Insert @Parents Select B.* From @Parents A Inner Join [T_Role] B On A.[RoleParentsID]=B.[RoleID] Where B.[RoleParentsID] Not In (Select Distinct [RoleParentsID] From @Parents)end
Return
End
--调用函数实现查询
select * from GetChild(id,level)
--删除
drop function GetChild