SQL Server嵌套游标异常：仅更新一行，其余行误判未找到

在SQL Server中使用嵌套游标编写存储过程时遇到异常：数百万条数据中仅能成功更新一行，其余行被错误判定为“未找到”，但实际数据确实存在。内部游标依赖外部游标的结果，附上存储过程代码，恳请排查错误原因。

USE [VIVA_LOAD]
GO
/****** Object:  StoredProcedure [dbo].[PAYSHOP_UPDATE_SAFT_NELSON_TESTE]    Script Date: 21/05/2025 19:47:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PAYSHOP_UPDATE_SAFT_NELSON_TESTE]
AS 
BEGIN

DECLARE @v_inv_date  NVARCHAR(256);
DECLARE @data1 datetime; 
DECLARE @entry_date datetime; 
DECLARE @data2 datetime;
DECLARE @V_CARD_LOAD_ID NVARCHAR(256);
DECLARE @V_CARD_serial  NVARCHAR(256);

DECLARE @V_InvoiceNo  NVARCHAR(256);
DECLARE @V_InvoiceDate  datetime;
DECLARE @V_SystemEntryDate datetime;
DECLARE @V_Description  NVARCHAR(256);
DECLARE @V_ProductCode  NVARCHAR(256);


DECLARE Data_Invoice_Usar CURSOR FOR     
SELECT hh.InvoiceNo, hh.InvoiceDate, hh.SystemEntryDate, hh.Description,hh.ProductCode  
FROM PayshopInvoiceLines2 hh 
where Estado_Registos=0;

/*DROP INDEX I_Update_Saft
    ON VIVA_LOAD.dbo.PayshopInvoiceLines2;
CREATE UNIQUE INDEX I_Update_Saft ON VIVA_LOAD.dbo.PayshopInvoiceLines2 (InvoiceNo,InvoiceDate,SystemEntryDate,Description,ProductCode);
*/
        OPEN Data_Invoice_Usar
        FETCH NEXT FROM Data_Invoice_Usar into @V_InvoiceNo, @V_InvoiceDate, @V_SystemEntryDate, @V_Description, @V_ProductCode 

         WHILE @@FETCH_STATUS = 0
            BEGIN

              
                SET @data1=dateadd(month, datediff(month, 0, @V_InvoiceDate), 0)
                               
                SET @data2 = dateadd(MONTH, datediff(MONTH, 1, @V_InvoiceDate)+1, -1);
        
   DECLARE viva_load_cards CURSOR
    FOR
      select cl.CardLoadID, Replace(cr.CardSerialNr,'.','')
      from VIVA_LOAD.dbo.CardLoad cl with(nolock)
        left JOIN VIVA_LOAD.dbo.Product AS p with(nolock) ON p.ProductID = cl.ProductID
        left JOIN VIVA_LOAD.dbo.CardLoadExec AS cle with(nolock) ON cl.CardLoadID = cle.CardLoadID
        inner join viva_load.dbo.CardRead cr with(nolock) on cr.CardReadId = cl.CardReadID
      where 1=1
      and cl.Timestamp >= @data1 and cl.Timestamp <= @data2 -- mes do SAFT
      and CONVERT(varchar(19), cle.Timestamp, 120) <= CONVERT(varchar(19), DATEADD(ss,5,@V_SystemEntryDate), 120) --Invoice.SystemEntryDate com margem de erro de 5 segundos
      and cl.SubEntityID = '12E9C77E-A078-4D3B-A0AB-80CD682E811C' -- Payshop
        and cr.CardSerialNr = @V_Description 
        and p.CatalogProductId = @V_ProductCode;

                OPEN viva_load_cards 
                FETCH NEXT FROM viva_load_cards into @V_CARD_LOAD_ID, @V_CARD_serial
                WHILE @@FETCH_STATUS = 0
                    
                    BEGIN

                              IF @V_CARD_LOAD_ID is not null
                                  IF @V_CARD_serial is not null
                           
                                       UPDATE PayshopInvoiceLines2
                                       SET Card_LoadId_Payshop = @V_CARD_LOAD_ID, Estado_Registos=1
                                       WHERE Description= @V_CARD_serial 
                                       and SystemEntryDate=@V_SystemEntryDate
                                       and Estado_Registos=0
                                       and ProductCode=@V_ProductCode
                                       and InvoiceNo=@V_InvoiceNo
                                       and InvoiceDate=@V_InvoiceDate;
                               ELSE
                                       UPDATE PayshopInvoiceLines2
                                       SET Card_LoadId_Payshop = @V_CARD_LOAD_ID, Estado_Registos=3
                                       wHERE Description= @V_CARD_serial
                                       and SystemEntryDate=@V_SystemEntryDate
                                       and Estado_Registos=0
                                       and ProductCode=@V_ProductCode
                                       and InvoiceNo=@V_InvoiceNo
                                       and InvoiceDate=@V_InvoiceDate;
                            
    FETCH NEXT FROM Data_Invoice_Usar into @V_InvoiceNo, @V_InvoiceDate, @V_SystemEntryDate, @V_Description, @V_ProductCode 

    FETCH NEXT FROM viva_load_cards into @V_CARD_LOAD_ID, @V_CARD_serial
                
                            
                          END;  
                            
                                    CLOSE viva_load_cards;
                            DEALLOCATE viva_load_cards;
     END;

                                UPDATE PayshopInvoiceLines2 
                                SET Estado_Registos=3
                                WHERE Estado_Registos=0;
CLOSE Data_Invoice_Usar;
DEALLOCATE Data_Invoice_Usar;

END;

1. 致命逻辑错误：外部游标被提前推进 #

内部游标循环中错误加入了FETCH NEXT FROM Data_Invoice_Usar语句，导致外部游标在处理第一行的内部循环时直接跳到下一行，后续大部分外部游标数据未进入处理逻辑，这是仅一行被更新的核心原因。

修复：删除内部循环里的FETCH NEXT FROM Data_Invoice_Usar语句，将其移到外部循环末尾，确保外部游标每处理完一行的所有内部逻辑后再推进：

-- 外部循环末尾添加
FETCH NEXT FROM Data_Invoice_Usar into @V_InvoiceNo, @V_InvoiceDate, @V_SystemEntryDate, @V_Description, @V_ProductCode

2. 数据匹配不一致问题 #

内部游标查询用Replace(cr.CardSerialNr,'.','')得到@V_CARD_serial，但查询条件是cr.CardSerialNr = @V_Description，UPDATE语句又用Description= @V_CARD_serial。若@V_Description包含.，则@V_CARD_serial是去掉.的版本，与原Description值不匹配，导致UPDATE找不到对应行。

修复：统一匹配规则，二选一即可：

• 查询时使用去掉.的条件：

  and Replace(cr.CardSerialNr,'.','') = @V_Description
  

• UPDATE时使用原@V_Description：

  WHERE Description= @V_Description 
  

3. 语法错误 #

内部游标循环的ELSE分支中，wHERE拼写错误，应为WHERE，会导致该分支的UPDATE语句执行失败。

4. 日期比较效率与准确性问题 #

通过转换为字符串比较日期CONVERT(varchar(19), cle.Timestamp, 120) <= CONVERT(varchar(19), DATEADD(ss,5,@V_SystemEntryDate), 120)，既影响性能，又可能因格式问题导致错误比较。直接用日期类型比较：

and cle.Timestamp <= DATEADD(ss,5,@V_SystemEntryDate)

5. 性能优化建议 #

数百万条数据使用嵌套游标会导致极低的执行效率，建议改用基于集合的UPDATE操作替代游标，例如通过JOIN直接关联表进行批量更新，大幅提升处理速度。

内容的提问来源于stack exchange，提问作者Nelson Soares