// 函数定义
// 调试MSSQL函数时,要打开“查询分析工具”--“查询”--“显示服务器跟踪”选项
CREATE FUNCTION f_Cid()
RETURNS @t_Level TABLE(LPARENTID bigint,LID bigint,Level int) // 定义返回table的结构
AS
BEGIN
DECLARE @Level int // 原来用作节点级别,这里主要用来关联父子节点
SET @Level=1
DECLARE id_cursor CURSOR FOR // 取出节点ID
SELECT lid
FROM Tb000000department
DECLARE @my_Id bigint // 存储节点ID的临时变量
OPEN id_cursor // 打开游标,遍历所有节点ID
FETCH NEXT FROM id_cursor INTO @my_id // FETCH NEXT & WHILE循环中有一对FETCH NEXT 语句
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT @t_Level SELECT @my_id,a.LID,@Level FROM Tb000000department a where a.LID=@my_id
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT @my_id,a.LID,@Level
FROM Tb000000department a,@t_Level b
WHERE a.LPARENTID=b.LID AND b.Level=@Level-1 // b.Level=@Level-1保证了只与本级节点联立查询
END
FETCH NEXT FROM id_cursor INTO @my_id
END
CLOSE id_cursor // 关闭并删除游标
DEALLOCATE id_cursor
RETURN
END
// 函数调用
select C.* from f_Cid() C