sql server 2005 递归查询

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/**
**
**author: zhyd
**date:20100331
**
**获取当前用户组织下的所有子组织（包括自己）
**/


ALTER proc [dbo].[SP_GET_OWNER_OFFICE](  
    @CREATE_OFFICE VARCHAR(100)
)  
AS 
BEGIN  
	with officeTemp(office_code)
	as
	(
		select office_code from sys_office where office_code = @CREATE_OFFICE
		union all
		select b.office_code from officeTemp a
		inner join sys_office b on b.supperior_office_code = a.office_code

	)

	select office_code from officeTemp
END  

[img]http://dl.iteye.com/upload/attachment/227483/1958a1af-b7a5-3a9d-a30e-554e2dddd133.jpg[/img]