USE [bpmdb]
GO
/****** Object: UserDefinedFunction [dbo].[f_GetTree] Script Date: 05/07/2015 12:44:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--模块:查询指定制定科目的汇总值
--时间:2014.12.23
--编写人: lj
--SELECT a.Code, a.ouname, lvl
--FROM BPMSysOUs a
-- JOIN dbo.f_GetTree(59) b
-- ON a.OUID = b.ID where OULevel ='部门'
ALTER FUNCTION [dbo].[f_GetTree]
(
@ID AS INT=NULL
)
RETURNS @r TABLE
(
ID INT,
lvl INT,
px VARBINARY(8000)
)
AS
BEGIN
DECLARE @lvl INT
SET @lvl = 0
IF ISNULL(@ID,0)<>0
INSERT @r VALUES(@ID,@lvl,CAST(@ID AS VARBINARY))
ELSE
INSERT @r
SELECT OUID, @lvl ,CAST(OUID AS VARBINARY)
FROM BPMSysOUs
WHERE ParentOUID is null
WHILE @@rowcount>0
BEGIN
SET @lvl = @lvl+1;
INSERT @r
SELECT a.OUID, @lvl ,b.px+CAST(a.OUID AS VARBINARY)
FROM BPMSysOUs a
JOIN @r b
ON a.ParentOUID = b.ID
AND b.lvl = @lvl-1
END
RETURN;
END
GO
GO
/****** Object: UserDefinedFunction [dbo].[f_GetTree] Script Date: 05/07/2015 12:44:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--模块:查询指定制定科目的汇总值
--时间:2014.12.23
--编写人: lj
--SELECT a.Code, a.ouname, lvl
--FROM BPMSysOUs a
-- JOIN dbo.f_GetTree(59) b
-- ON a.OUID = b.ID where OULevel ='部门'
ALTER FUNCTION [dbo].[f_GetTree]
(
@ID AS INT=NULL
)
RETURNS @r TABLE
(
ID INT,
lvl INT,
px VARBINARY(8000)
)
AS
BEGIN
DECLARE @lvl INT
SET @lvl = 0
IF ISNULL(@ID,0)<>0
INSERT @r VALUES(@ID,@lvl,CAST(@ID AS VARBINARY))
ELSE
INSERT @r
SELECT OUID, @lvl ,CAST(OUID AS VARBINARY)
FROM BPMSysOUs
WHERE ParentOUID is null
WHILE @@rowcount>0
BEGIN
SET @lvl = @lvl+1;
INSERT @r
SELECT a.OUID, @lvl ,b.px+CAST(a.OUID AS VARBINARY)
FROM BPMSysOUs a
JOIN @r b
ON a.ParentOUID = b.ID
AND b.lvl = @lvl-1
END
RETURN;
END
GO