- 论坛徽章:
- 0
|
- 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'
- end
- select isnull(#temp_table.id,'') from
- #temp_table where #temp_table.id <> ''
- OnError: if(@@Error <> 0)
- begin
- rollback transaction
- end
- else
- begin
- commit transaction
- end
复制代码 |
|