SQL Server cursor inside cursor

Question

I'm trying to use a cursor inside another cursor. I'm facing a problem that's: I'm only be able of update one row of all the millions of rows, and the others are considered as 'not found' which is not true.

The inside cursor depends of rows on the outside cursor.

Can anyone help me to understand what I'm doing wrong?

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;

The second FETCH NEXT FROM Data_Invoice_Usar into @V_InvoiceNo, @V_InvoiceDate, @V_SystemEntryDate, @V_Description, @V_ProductCode must be outside of the loop of the inner cursor, otherwise it will as you say, not work. This is why i prefer doing the WHILE 1 = 1 FETCH NEXT -version of cursors — siggemannen
– siggemannen, Commented May 23 at 13:42
Cursor are the slowest approach you can use for this. And nested cursors is just horrific. All you are doing here is a couple of update statements. These can, and should, be set based updates of the row by agonizing row approach. — Sean Lange
– Sean Lange, Commented May 23 at 14:11
While I fully agree with Sean in this case ^ ... more generally, and there are cases where cursors and nested cursors are practical, note that nested cursors can be fun when you are checking @@FETCH_STATUS, since this is global. I recently changed multiple nested cursors to a while loop with a cursor because one cursor's status affected the other's. — Aaron Bertrand
– Aaron Bertrand, Commented May 23 at 14:25
Pretty sure this cursor is full of bugs, the NULL checking of @V_CARD_serial is pointless because @V_CARD_serial comes from same source as @V_Description and @V_Description cannot be NULL, or the whole cursor will never loop anything. @OP, throw this thing out and start over — siggemannen
– siggemannen, Commented May 23 at 14:37

Charlieface · Accepted Answer · 2025-05-23 15:52:09Z

This doesn't need cursors at all. You can do a single joined UPDATE statement to do all the updates.

Your existing code in any case had many issues:

The outer FETCH was in the wrong place.
wHERE Description= @V_CARD_serial isn't going to work if @V_CARD_serial is null. You need IS NULL or IS DISTINCT FROM instead.
Don't use NOLOCK unless you really, really know what it does, and are prepared to deal with the consequences of incorrect data.
LEFT JOIN followed by a WHERE on that table is logically an inner-join.
Use DATETRUNC instead of mucking with DATEADD.
On older versions of SQL Server you can use EOMONTH or DATEFROMPARTS to get the same thing.
Don't use CONVERT(varchar for comparing dates. Either compare the exact values, or calculate ranges using DATEADD etc and use >= AND <.

UPDATE pil
SET Card_LoadId_Payshop = cl.CardLoadID,
    Estado_Registos = IIF(REPLACE(cr.CardSerialNr, '.', '') IS NULL, 1, 3)
FROM PayshopInvoiceLines2 pil

JOIN VIVA_LOAD.dbo.CardLoad cl
   ON pil.Description IS NOT DISTINCT FROM REPLACE(cr.CardSerialNr, '.', '')
  AND cl.Timestamp >= DATETRUNC(MONTH, pil.InvoiceDate)
  AND cl.Timestamp <= DATEADD(MONTH, 1, DATETRUNC(MONTH, pil.InvoiceDate) -- mes do SAFT
JOIN VIVA_LOAD.dbo.Product AS p ON p.ProductID = cl.ProductID
JOIN VIVA_LOAD.dbo.CardLoadExec AS cle ON cl.CardLoadID = cle.CardLoadID
JOIN viva_load.dbo.CardRead cr on cr.CardReadId = cl.CardReadID

WHERE pil.Estado_Registos = 0
  AND cle.Timestamp <= DATEADD(ss, 5, pil.SystemEntryDate) --Invoice.SystemEntryDate com margem de erro de 5 segundos
  AND cl.SubEntityID = '12E9C77E-A078-4D3B-A0AB-80CD682E811C' -- Payshop
;

UPDATE PayshopInvoiceLines2 
SET Estado_Registos = 3
WHERE Estado_Registos = 0;

Collectives™ on Stack Overflow

SQL Server cursor inside cursor

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related