遍历表,执行存储过程的方法
1、使用“表变量 ”
declare @Row int,
@Rows int,
@DANo char(23),
@DATime datetime,
@LogTime datetime,
@MeterType char(4),
@MeterNo char(20),
@Qty decimal(18,6)
declare @t table (
Row int identity(1,1) not null,
DANo char(23) not null,
DATime datetime not null,
LogTime datetime not null,
MeterType char(4) not null,
MeterNo char(20) not null,
Qty decimal(18,6) null
)
insert into @t
select DANo,DATime,LogTime,MeterType,MeterNo,Qty
set @Rows = @@ROWCOUNT
set @Row = 1
while (@Row <=@Rows)
begin
select @DANo = DANo,@DATime= DATime,@LogTime= LogTime,@MeterType = MeterType,@MeterNo = MeterNo ,@Qty = Qty
from @T where row = @Row
exec Usp_DAListForEnergyDataDetail @DANo,@DATime,@LogTime,@MeterType,@MeterNo,@Qty
set @Row = @Row + 1
end
@DANo char(23),
@DATime datetime,
@LogTime datetime,
@MeterType char(4),
@MeterNo char(20),
@Qty decimal(18,6)
declare @t table (
Row int identity(1,1) not null,
DANo char(23) not null,
DATime datetime not null,
LogTime datetime not null,
MeterType char(4) not null,
MeterNo char(20) not null,
Qty decimal(18,6) null
)
insert into @t
select DANo,DATime,LogTime,MeterType,MeterNo,Qty
set @Rows = @@ROWCOUNT
set @Row = 1
while (@Row <=@Rows)
begin
select @DANo = DANo,@DATime= DATime,@LogTime= LogTime,@MeterType = MeterType,@MeterNo = MeterNo ,@Qty = Qty
from @T where row = @Row
exec Usp_DAListForEnergyDataDetail @DANo,@DATime,@LogTime,@MeterType,@MeterNo,@Qty
set @Row = @Row + 1
end
2、使用游标
declare cursor_test cursor local forward_only static read_only
for
select DANo,DATime,LogTime,MeterType,MeterNo,Qty from table_name
open cursor_test
fetch next from cursor_test into @DANo,@DATime,@LogTime,@MeterType,@MeterNo,@Qty
while(@@fetch_status=0)
begin
exec Usp_DAListForEnergyDataDetail @DANo,@DATime,@LogTime,@MeterType,@MeterNo,@Qty
fetch next from cursor_test into @DANo,@DATime,@LogTime,@MeterType,@MeterNo,@Qty
end
close cursor_test
for
select DANo,DATime,LogTime,MeterType,MeterNo,Qty from table_name
open cursor_test
fetch next from cursor_test into @DANo,@DATime,@LogTime,@MeterType,@MeterNo,@Qty
while(@@fetch_status=0)
begin
exec Usp_DAListForEnergyDataDetail @DANo,@DATime,@LogTime,@MeterType,@MeterNo,@Qty
fetch next from cursor_test into @DANo,@DATime,@LogTime,@MeterType,@MeterNo,@Qty
end
close cursor_test
deallocate cursor_test
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步