frank oo java

CREATE TABLE departments (deptid  VARCHAR(20),
            deptname VARCHAR(200),
            empcount INT,
            superdept  VARCHAR(20))

superdept -----上级部门id

create   function fnDeptChildren (@id varchar(20))

returns @tb table (deptid varchar(20),superdept varchar(20))
AS
begin
insert @tb select deptid ,superdept from departments where deptid = @id
while @@rowcount > 0
	insert @tb select deptid ,superdept from departments 		
                where superdept in (select deptid from @tb)
		and deptid not in (select deptid from @tb)

return
end

select deptid from fnDeptChildren('部门编号')

Trackback: http://tb.donews.net/TrackBack.aspx?PostId=652544