--
查询父单位的函数
CREATE Function GetParentUnit( @UnitId bigint , @Level int )
Returns @Parents Table ( [ UnitId ] int , [ UnitName ] nvarchar ( 64 ), [ ParentUnitId ] int )
As
Begin
If @Level < 1 -- 如果参数@Level小于“1”,则得到所有父单位
Begin
Insert @Parents
Select UnitId,UnitName,ParentUnitId
From [ T_Unit ]
Where [ UnitId ] = ( Select [ ParentUnitId ]
From [ T_Unit ] Where [ UnitId ] = @UnitId )
While @@ROWCOUNT > 0
Begin
Insert @Parents
Select B.UnitId,B.UnitName,B.ParentUnitId
From @Parents A Inner Join [ T_Unit ] B
On A. [ ParentUnitId ] = B. [ UnitId ]
Where B. [ ParentUnitId ] Not In
( Select Distinct [ ParentUnitId ] From @Parents )
End
End
Else
Begin
Set @level = @Level - 1
Insert @Parents
Select UnitId,UnitName,ParentUnitId
From [ T_Unit ]
Where [ UnitId ] = ( Select [ ParentUnitId ]
From [ T_Unit ] Where [ UnitId ] = @UnitId )
While @@ROWCOUNT > 0 And @level > 0
Begin
Set @level = @Level - 1
Insert @Parents
Select B.UnitId,B.UnitName,B.ParentUnitId
From @Parents A Inner Join [ T_Unit ] B
On A. [ ParentUnitId ] = B. [ UnitId ]
Where B. [ ParentUnitId ] Not In
( Select Distinct [ ParentUnitId ] From @Parents )
End
End
Return
End
-- 查询子单位的函数
CREATE Function GetChildUnit( @UnitId bigint , @Level int )
Returns @Child Table ( [ UnitId ] int , [ UnitName ] varchar ( 64 ), [ ParentUnitId ] int )
As
Begin
If @Level < 1 -- 如果参数@Level小于“1”,则得到所有子单位
Begin
Insert @Child
Select UnitId,UnitName,ParentUnitId
From [ T_Unit ] Where [ ParentUnitId ] = @UnitId
While @@ROWCOUNT > 0
Begin
Insert @Child
Select B.UnitId,B.UnitName,B.ParentUnitId
From @Child A Inner Join [ T_Unit ] B
On A. [ UnitId ] = B. [ ParentUnitId ]
Where B. [ ParentUnitId ] Not In
( Select Distinct [ ParentUnitId ] From @Child )
End
End
Else
Begin
Set @level = @Level - 1
Insert @Child
Select UnitId,UnitName,ParentUnitId
From [ T_Unit ] Where [ ParentUnitId ] = @UnitId
While @@ROWCOUNT > 0 And @level > 0
Begin
Set @level = @Level - 1
Insert @Child
Select B.UnitId,B.UnitName,B.ParentUnitId
From @Child A Inner Join [ T_Unit ] B
On A. [ UnitId ] = B. [ ParentUnitId ]
Where B. [ ParentUnitId ] Not In
( Select Distinct [ ParentUnitId ] From @Child )
End
End
Return
End
-- 查询
select * from GetParentUnit( 3 , 2 )
select * from GetChildUnit( 1 , 2 )
-- 删除函数
drop Function GetParentsUnit
drop Function GetChildUnit
CREATE Function GetParentUnit( @UnitId bigint , @Level int )
Returns @Parents Table ( [ UnitId ] int , [ UnitName ] nvarchar ( 64 ), [ ParentUnitId ] int )
As
Begin
If @Level < 1 -- 如果参数@Level小于“1”,则得到所有父单位
Begin
Insert @Parents
Select UnitId,UnitName,ParentUnitId
From [ T_Unit ]
Where [ UnitId ] = ( Select [ ParentUnitId ]
From [ T_Unit ] Where [ UnitId ] = @UnitId )
While @@ROWCOUNT > 0
Begin
Insert @Parents
Select B.UnitId,B.UnitName,B.ParentUnitId
From @Parents A Inner Join [ T_Unit ] B
On A. [ ParentUnitId ] = B. [ UnitId ]
Where B. [ ParentUnitId ] Not In
( Select Distinct [ ParentUnitId ] From @Parents )
End
End
Else
Begin
Set @level = @Level - 1
Insert @Parents
Select UnitId,UnitName,ParentUnitId
From [ T_Unit ]
Where [ UnitId ] = ( Select [ ParentUnitId ]
From [ T_Unit ] Where [ UnitId ] = @UnitId )
While @@ROWCOUNT > 0 And @level > 0
Begin
Set @level = @Level - 1
Insert @Parents
Select B.UnitId,B.UnitName,B.ParentUnitId
From @Parents A Inner Join [ T_Unit ] B
On A. [ ParentUnitId ] = B. [ UnitId ]
Where B. [ ParentUnitId ] Not In
( Select Distinct [ ParentUnitId ] From @Parents )
End
End
Return
End
-- 查询子单位的函数
CREATE Function GetChildUnit( @UnitId bigint , @Level int )
Returns @Child Table ( [ UnitId ] int , [ UnitName ] varchar ( 64 ), [ ParentUnitId ] int )
As
Begin
If @Level < 1 -- 如果参数@Level小于“1”,则得到所有子单位
Begin
Insert @Child
Select UnitId,UnitName,ParentUnitId
From [ T_Unit ] Where [ ParentUnitId ] = @UnitId
While @@ROWCOUNT > 0
Begin
Insert @Child
Select B.UnitId,B.UnitName,B.ParentUnitId
From @Child A Inner Join [ T_Unit ] B
On A. [ UnitId ] = B. [ ParentUnitId ]
Where B. [ ParentUnitId ] Not In
( Select Distinct [ ParentUnitId ] From @Child )
End
End
Else
Begin
Set @level = @Level - 1
Insert @Child
Select UnitId,UnitName,ParentUnitId
From [ T_Unit ] Where [ ParentUnitId ] = @UnitId
While @@ROWCOUNT > 0 And @level > 0
Begin
Set @level = @Level - 1
Insert @Child
Select B.UnitId,B.UnitName,B.ParentUnitId
From @Child A Inner Join [ T_Unit ] B
On A. [ UnitId ] = B. [ ParentUnitId ]
Where B. [ ParentUnitId ] Not In
( Select Distinct [ ParentUnitId ] From @Child )
End
End
Return
End
-- 查询
select * from GetParentUnit( 3 , 2 )
select * from GetChildUnit( 1 , 2 )
-- 删除函数
drop Function GetParentsUnit
drop Function GetChildUnit