Ecology9 组织权限 查询所有上级部门ID

查询所有上级部门（SQLServer2012）

WITH TEMP
     AS (SELECT Id,
                departmentName,
                supdepid
         FROM   hrmdepartment
         WHERE  Id = 935
         UNION ALL
         SELECT B.Id,
                B.departmentName,
                B.supdepid
         FROM   TEMP A
                INNER JOIN hrmdepartment B
                        ON B.Id = A.supdepid)
SELECT Id,
       departmentName,
       supdepid
FROM   TEMP

自定义函数 - 查询所有上级部门ID（SQLServer2012）

CREATE FUNCTION CF_GET_ALL_SUPDEPTID (
	@deptId INT
)
RETURNS VARCHAR(100)
AS
BEGIN

	DECLARE @id INT, @name VARCHAR(100), @supid INT
	DECLARE @cur CURSOR
	DECLARE @ret VARCHAR(100)
	
	SET @ret = ''
	SET @cur = CURSOR FOR WITH TEMP AS (
			SELECT ID, SUPDEPID FROM HRMDEPARTMENT WHERE ID=@deptId
			UNION ALL
			SELECT B.ID, B.SUPDEPID FROM TEMP A
			 INNER JOIN HRMDEPARTMENT B ON B.ID=A.SUPDEPID)
		SELECT ID FROM TEMP
		
	OPEN @cur
	FETCH NEXT FROM @cur INTO @id
	WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @ret = CAST(@ret AS VARCHAR) + ',' + CAST(@id AS VARCHAR)
			FETCH NEXT FROM @cur INTO @id
		END
	CLOSE @cur
	DEALLOCATE @cur
	
	IF @ret <> ''
	BEGIN
		SET @ret = SUBSTRING(@ret, 2, LEN(@ret))
	END
	
	RETURN @ret
	
END

调用

SELECT DBO.CF_GET_ALL_SUPDEPTID(984)

-- 返回结果
-- 984,953,906,1142
-- 其中第一个值：948是本部门ID，其他都是上级部门ID