這個不一定要用遞歸啊,你可以用個循環(huán)來處理,
比如要取上級機(jī)構(gòu)可以這樣
CREATE PROCEDURE dbo.get_uporg_sp
@orgcode varchar(10)
AS
declare @uporgcode varchar(10),@pid int
create table #tab1(orgcode varchar(10) not null)
select @pid = pid from T_ORG_INFO where orgcode = @orgcode
while @pid is not null
begin
select @uporgcode = orgcode from T_ORG_INFO where id = @pid
insert into #tab1(orgcode) values(@uporgcode)
select @pid = pid from T_ORG_INFO where id = @pid
end
select * from #tab1作者: mfkpie8 時間: 2013-07-30 22:51
ALTER PROCEDURE "DBA"."UPS_PRODUCT_CODE_SELECT_CHILD_ID"(@arg_id char(30))
as
begin transaction
declare @count integer
declare @ic integer
declare @Version char(30)
select id=@arg_id,pross_flag='Y',parent_id='' into #temp_table
from product_code where product_code = @arg_id
if(@@Error <> 0) goto OnError
select id=product_code,pross_flag='N',parent_id=parent_id into #temp_table2
from product_code where ltrim(isnull(parent_id,'')) = @arg_id
if(@@Error <> 0) goto OnError
insert into #temp_table(id,pross_flag,parent_id)
select id,'N',parent_id from #temp_table2
if(@@Error <> 0) goto OnError
delete from #temp_table2
if(@@Error <> 0) goto OnError
select @count=1
while(@count >= 1)
begin
insert into #temp_table2(id,pross_flag,parent_id)
select product_code.product_code,'N',product_code.parent_id from
product_code where ltrim(isnull(product_code.parent_id,'')) <> '' and product_code.parent_id = any(select #temp_table.id from #temp_table where pross_flag = 'N')
if(@@Error <> 0) goto OnError
update #temp_table set pross_flag = 'Y'
if(@@Error <> 0) goto OnError
insert into #temp_table(id,pross_flag,parent_id)
select id,pross_flag,parent_id from #temp_table2
if(@@Error <> 0) goto OnError
delete from #temp_table2
if(@@Error <> 0) goto OnError
select @count = isnull(COUNT(*),0) from #temp_table where pross_flag = 'N'