MS SQL 递归函数

/*
select *,dbo.getDeptTree(id) as DeptTree from sysdept
递归函数
*/
CREATE  function getDeptTree(@NodeId int)
returns varchar(8000)
as
begin
    declare @ret varchar(8000),@ParentId int,@len int
    set @len = 0
    set @ret = ''
    select @ret=deptname+@ret,@ParentId=subid from sysdept where id=@NodeId
    while @@rowcount<>0
    begin
        set @NodeId=@ParentId
        -- set @ret = replace(@ret,'─','  ')
        select @ret='|─'+@ret,@ParentId=subid from sysdept where id=@NodeId
    end
    return @ret
end