遍历表，执行存储过程的方法

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

 

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

deallocate cursor_test