SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[UP_Department_GetAllChildDeptGUIDByLevels]
@DeptGUID uniqueidentifier,
@StartLevel int,
@EndLevel int
AS
BEGIN
SET NOCOUNT ON
declare @str varchar(8000)
Declare @sql varchar(8000)
declare @str1 varchar(8000)
declare @str2 varchar(8000)
declare @CountBy int
declare @i int
set @i = 0
set @CountBy = dbo.DeptLevel_CountBy(@StartLevel,@EndLevel)
set @str1 = 'select guid from Department(nolock) where parentguid = '''+cast(@DeptGUID as varchar(50))+''''
set @str2 = 'select guid from Department(nolock) where parentguid in ('
set @str = @str1
set @sql = @str1
while @i < @CountBy
begin
set @str = @str2 + @str + ')'
set @sql = @sql + ' union ' + @str
set @i = @i + 1
end
--print @sql
execute(@sql)
END
SET ANSI_NULLS OFF
CREATE FUNCTION [dbo].[DeptLevel_CountBy]
(
@StartLevel int,
@EndLevel int
)
RETURNS int
AS
BEGIN
DECLARE @CountBy int
SELECT @CountBy=count(LevelCode)
FROM DeptLevel
WHERE LevelCode BETWEEN @StartLevel AND @EndLevel
RETURN @CountBy
END
其中LevelCode为部门的层级,此算法取得子部门ID后,怎么操作就看你了。
速度很快，适合大型企业。
