如何遍历表，根据表的每一条记录，执行下存储过程。
遍历表：linshidaList
表linshidaList对应的列：
CREATE TABLE [dbo].[linshidaList](
[DANo] [char](32) NOT NULL,
[DATime] [datetime] NOT NULL,
[LogTime] [datetime] NOT NULL,
[DAType] [char](3) NULL,
[MeterType] [char](4) NOT NULL,
[MeterNo] [char](20) NOT NULL,
[Qty] [decimal](18, 6) NULL,
[Qty01] [decimal](18, 6) NULL,
[Qty02] [decimal](18, 6) NULL,
[Qty11] [decimal](18, 6) NULL,
[Qty12] [decimal](18, 6) NULL,
[lable] [char](1) NOT NULL
)
遍历每一条记录执行的存储过程参数如下：
ALTER PROCEDURE [dbo].[Usp_DAListForEnergyDataDetail] (
@DANo Char(32),
@DATime Datetime,
@LogTime Datetime,
@MeterType Char(4),
@MeterNo Char(20),
@Qty Decimal(18,6)
)
[email protected]，[email protected]，[email protected]，[email protected]，[email protected]，[email protected]
------解决方案--------------------
- SQL code
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 @tselect DANo,DATime,LogTime,MeterType,MeterNo,Qtyset @Rows = @@ROWCOUNTset @Row = 1while (@Row <[email protected])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 + 1end
------解决方案--------------------
问题1：这个叫表变量
问题2：@@ROWCOUNT 是SQL Server全局变量，表示执行上一语句所影响的行数
当你插入数据为10行时 @@ROWCOUNT =10
------解决方案--------------------
- SQL code
--当然可以declare cursor_test cursor local forward_only static read_onlyfor select DANo,DATime,LogTime,MeterType,MeterNo,Qty from table_nameopen cursor_testfetch next from cursor_test into @DANo,@DATime,@LogTime,@MeterType,@MeterNo,@Qtywhile(@@fetch_status=0)begin exec Usp_DAListForEnergyDataDetail @DANo,@DATime,@LogTime,@MeterType,@MeterNo,@Qty fetch next from cursor_test into @DANo,@DATime,@LogTime,@MeterType,@MeterNo,@Qtyendclose cursor_testdeallocate cursor_test
------解决方案--------------------
不建议使用游标
1：游标效率低
2：游标会造成锁表
3：除非在很难处理的地方，否则不使用游标
------解决方案--------------------