SQL临时表递归查询子信息并返回记录的代码

declare @q_id uniqueidentifier
set @q_id = dbo.uf_getparamvaluebyname(@params,'@指标id');
declare @qaexp_id char(36) --指标属性公式id
set @qaexp_id='3d2b8f3f-0b7e-46fd-9b33-050f846c2869'
declare @temp_qid table(qid char(36),expvalue nvarchar(max)) --临时表变量获得指标根id
declare @qidtemp char(36),@express nvarchar(4000)
declare @k int=2 --层次
declare @pattern nvarchar(2)='id' --指标公式拆分字段
declare @charidex int --指标对应的索引
if(@optype = '根据指标id查找公式所有子指标')
begin
create table #tempquotastruct --创建临时表#tmpstruct
(
qid char(36), --创建一个id用来存储指标id
pid char(36), --用来存储该指标相关的id
ordervalue int --层级关系
)
insert #tempquotastruct(qid,ordervalue)values(@q_id,1)
while exists(select q_id from eots_quotaattributevalue where qa_id=@qaexp_id and q_id in (select qid from #tempquotastruct where ordervalue=@k-1) )
begin
insert into @temp_qid select q_id,qav_value from eots_quotaattributevalue whereqa_id=@qaexp_idand q_id in (select qid from #tempquotastruct whereordervalue=@k-1)
while exists(select qid from @temp_qid)
begin
select top 1 @qidtemp=qid, @express=expvalue from @temp_qid
print @qidtemp
set @express=rtrim(ltrim(@express))
set @charidex=charindex(@pattern,@express)
while @charidex>=1
begin
insert into #tempquotastruct(qid,pid,ordervalue)values(substring(@express,@charidex+2,36),@qidtemp,@k)
set @express=substring(@express,@charidex+38,len(@express)-@charidex+37)
set @charidex=charindex(@pattern,@express)
end
delete from @temp_qid where qid = @qidtemp
end
set @k=@k+1
end
select a.*,b.q_name, c.qav_value as q_formula from #tempquotastruct a,eots_quota b,eots_quotaattributevalue c where a.qid=b.q_id and a.qid=c.q_id and c.qa_id='3d2b8f3f-0b7e-46fd-9b33-050f846c2869'

此sql是对标模块的临时表查询，他能查出一个指标下面的多个子指标的公式，并分层级显示 @指标id=#*1*#;#*1*#08bea0aaf-0ed2-4c9b-8c20-8c5bd919db6f#*1*#
摘自eots 存储过程up_eots_get_quotastruct
临时表循环的关键就是下面的语句, select top 1 from #tep,用一个变量循环取值，然后删除循环中的值

insert into #tep select optname,value,major_version from msreplication_options
while exists(select a from #tep)
begin
select top 1 @tempa =a,@tempc =c from #tep
delete #tep where a=@tempa
end