如何遍历表,根据表的每一条记录,执行下存储过程。遍历表: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))“@DANo参数”对应表linshidaList“DANo列”,“@DATime参数”对应表linshidaList“DATime列”,“@LogTime参数”对应表linshidaList“LogTime列”,“@MeterType参数”对应表linshidaList“MeterType列”,“@MeterNo参数”对应表linshidaList“MeterNo列”,“@Qty参数”对应表linshidaList“Qty列”
[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))“@DANo参数”对应表linshidaList“DANo列”,“@DATime参数”对应表linshidaList“DATime列”,“@LogTime参数”对应表linshidaList“LogTime列”,“@MeterType参数”对应表linshidaList“MeterType列”,“@MeterNo参数”对应表linshidaList“MeterNo列”,“@Qty参数”对应表linshidaList“Qty列”
解决方案 »
- ?????怎么在一组数字中随机取3个总和为24的数字?????
- 请大家帮忙修改下SQL语句,谢谢!
- 关于约束.求救.
- 简单的过程
- 用命令行启动SQL服务问题
- 求超难SQL,也可用存储过程
- 请教一个问题,请各位大虾不吝赐教
- 突然连接不上服务器。。。(在线等)
- 汇总两列条件的数理
- 帮忙解释set @TempStr = N'update XZ_XianYiRen set IsDel = ' + char(39) + '1' + char(39) + N' where ' + @ColumnName +N' in ('+@De
- 一个简单的用in做限定的嵌套查询的sql语句,但没有结果
- 请问什么叫版本管理
@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
谢谢您,您的思想是这样的吧,如下。还有几个问题想请教下1、声明变量,用来存储遍历数据2、创建表,存储在变量中。问题一:像这样的在脚本创建的表,叫做什么表呢???它不是临时表吧???3、填充表@t,设置行数。问题二:“set @Rows = @@ROWCOUNT”,“@@ROWCOUNT”从哪里来的呢???为什么可以表示“@t表”的行数???4、循环执行存储过程
问题2:@@ROWCOUNT 是SQL Server全局变量,表示执行上一语句所影响的行数
当你插入数据为10行时 @@ROWCOUNT =10
declare cursor_test cursor local forward_only static read_only
for
select DANo,DATime,LogTime,MeterType,MeterNo,Qty from table_nameopen cursor_test
fetch 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,@Qty
endclose cursor_test
deallocate cursor_test
谢谢您,谢谢您的思路是:
1、定义静态制度游标2、打开游标并为其赋初值。3、循环游标,并执行存储过程。问题:
“@@fetch_status”这个变量表示什么???
“deallocate cursor_test”是销毁游标???
来获取总行书,然后再用开始设置的变量等执行你后面的循环,这样会不会好点,反正都是要遍历表。