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