SQL server中存错过程和游标
在sql server中，存错过程中怎么用多个游标？我想通过游标来把存错过程返回的结果集，以list的结果集返回出来。因为在存错过程中有多个select，返回多个结果集，
例如这个存储过程：
create proc proc_queryCurrUserAudList(
@sys_oper_id varchar(8), --输入参数
@ot_audtt_audresult numeric(12,0) out,
@ot_audtt_doevent numeric(12,0) output,
@ot_audtt_infotype numeric(12,0)output, --输入输出参数
@ot_audtt_id numeric(12,0) out, --输出参数
@ot_audtt_infoid numeric(12,0) out,
@colname numeric(12,0) out
)
as
--查询关联表"JC_LOG"
select @ot_audtt_id=o.AUD_ID,@ot_audtt_infoid=o.INFO_ID,@ot_audtt_infotype=o.INFO_TYPE,@ot_audtt_doevent=o.DO_EVENT,@ot_audtt_audresult=o.AUD_RESULT,@colname=j.LOG_CODE
from OT_AUDIT o,JC_LOG j
where 1=1 and o.INFO_ID=j.LOG_ID and o.INFO_TYPE=@ot_audtt_infotype and o.PUB_STATUS=0
and o.DO_EVENT=@ot_audtt_doevent
and o.AUD_OBJ_TYPE=390003 and ','+o.AUD_OBJ+',' like '%,'+@sys_oper_id+',%'
order by j.LOG_CODE desc,o.CREATE_DATE desc;
--查询关联表"JC_LOG_FEEDBACK"
select @ot_audtt_id=o.AUD_ID,@ot_audtt_infoid=o.INFO_ID,@ot_audtt_infotype=o.INFO_TYPE,@ot_audtt_doevent=o.DO_EVENT,@ot_audtt_audresult=o.AUD_RESULT,@colname=j.LOG_CODE
from OT_AUDIT o,JC_LOG_FEEDBACK j
where 1=1 and o.INFO_ID=j.FB_ID and o.INFO_TYPE=@ot_audtt_infotype and o.PUB_STATUS=1
and o.DO_EVENT=@ot_audtt_doevent
and o.AUD_OBJ_TYPE=390003 and ','+o.AUD_OBJ+',' like '%,'+@sys_oper_id+',%'
order by j.LOG_CODE desc,o.CREATE_DATE desc;
go 数据库
[解决办法]
多个结果集返回table[0],table[1],table[2]....
要把table转换成list
[解决办法]
用两个ｓｅｌｅｃｔ的结果ｕｎｉｏｎ一下，联机帮助一下ｕｎｉｏｎ的用法
eｇ:
ｓｅｌｅｃｔ ｃｏｌ1 ｆｒｏｍ ｔｂ1
ｕｎｉｏｎ
ｓｅｌｅｃｔ ｃｏｌ2 ｆｒｏｍ ｔｂ2
