Cursorfetch: INTO 列表中声明的变量数目必须与所选列的数目相同。
0
悬赏园豆：50
[待解决问题]
---游标循环遍历--
begin
declare @cardNo varchar(50),@websiteRegTime varchar(50),@payAmount varchar(50),@merNo varchar(40),@re varchar(50),@uDate varchar(50) ,@gDate varchar(50),@appOper varchar(50)
declare @id int, @refusalsNumber int, @transNumber int ,@appStatus int
--申明游标为
declare order_cursor cursor
for (select id ,'100012' as mer_no,card_no,refusals_number,trans_number,website_reg_time,pay_amount,1 as approval_status,'同步' as remark,GETDATE() as upload_time ,GETDATE() as approval_time,'admin' as approval_operator from gw_merchant_whiteList where mer_no in ('100011') and approval_status=1)
--打开游标--
open order_cursor
--开始循环游标变量--
fetch next from order_cursor into @id, @merNo,@cardNo,@refusalsNumber, @transNumber,@websiteRegTime ,@payAmount,@appStatus,@re,@uDate,@gDate,@appOper
while @@FETCH_STATUS = 0 --返回被 FETCH语句执行的最后游标的状态--
begin
IF not EXISTS(select count(1) from gw_merchant_whiteList where mer_no=@merNo and card_no= @cardNo and approval_status=1)
BEGIN
INSERT INTO [dbo].[gw_merchant_whiteList]([mer_no], [card_no], [refusals_number], [trans_number], [website_reg_time], [pay_amount], [approval_status], [remark], [upload_time], [approval_time], [approval_operator]) VALUES (@merNo, @cardNo, @refusalsNumber, @transNumber, @websiteRegTime, @payAmount, @appStatus, @re, @gDate, @gDate, @appOper);
END
fetch next from order_cursor into @id --转到下一个游标，没有会死循环
end
close order_cursor --关闭游标
deallocate order_cursor --释放游标
end
go
所有回答(2)
0
转到下一个游标那里少了两个字段：FETCH NEXT FROM order_cursor INTO @id, @merNo, @cardNo; --转到下一个游标，没有会死循环
0
--申明游标为
declare order_cursor cursor
for (select id ,'100012' as mer_no,card_no,refusals_number,trans_number,website_reg_time,pay_amount,1 as approval_status,'同步' as remark,GETDATE() as upload_time ,GETDATE() as approval_time,'admin' as approval_operator from gw_merchant_whiteList where mer_no in ('100011') and approval_status=1)
--打开游标--
open order_cursor
--开始循环游标变量--
fetch next from order_cursor into @id, @merNo,@cardNo,@refusalsNumber, @transNumber,@websiteRegTime ,@payAmount,@appStatus,@re,@uDate,@gDate,@appOper
while @@FETCH_STATUS = 0 --返回被 FETCH语句执行的最后游标的状态--
begin
IF not EXISTS(select count(1) from gw_merchant_whiteList where mer_no=@merNo and card_no= @cardNo and approval_status=1)
BEGIN
INSERT INTO [dbo].[gw_merchant_whiteList]([mer_no], [card_no], [refusals_number], [trans_number], [website_reg_time], [pay_amount], [approval_status], [remark], [upload_time], [approval_time], [approval_operator]) VALUES (@merNo, @cardNo, @refusalsNumber, @transNumber, @websiteRegTime, @payAmount, @appStatus, @re, @gDate, @gDate, @appOper);
END
fetch next from order_cursor into @id, @merNo,@cardNo,@refusalsNumber, @transNumber,@websiteRegTime ,@payAmount,@appStatus,@re,@uDate,@gDate,@appOper --转到下一个游标，没有会死循环
end
close order_cursor --关闭游标
deallocate order_cursor --释放游标
