How to commit inside a CURSOR Loop?

I am trying to see if its possible to perform Update within a cursor loop and this updated data gets reflected during the second iteration in the loop.

DECLARE cur CURSOR
FOR SELECT [Product], [Customer], [Date], [Event] FROM MyTable
WHERE [Event] IS NULL

OPEN cur
FETCH NEXT INTO @Product, @Customer, @Date, @Event
WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT * FROM MyTable WHERE [Event] = 'No Event' AND [Date] < @DATE
  -- Now I update my Event value to 'No Event' for records whose date is less than @Date
  UPDATE MyTable SET [Event] = 'No Event' WHERE [Product]开发者_如何学运维 = @Product AND [Customer] = @Customer AND [Date] < @DATE
   FETCH NEXT INTO @Product, @Customer, @Date, @Event
END
CLOSE cur
DEALLOCATE cur

Assume when the sql executes the Event column is NULL for all records In the above sql, I am doing a select inside the cursor loop to query MyTable where Event value is 'No Event' but the query returns no value even though I am doing an update in the next line. So, I am thinking if it is even possible to update a table and the updated data get reflected in the next iteration of the cursor loop.

Thanks for any help, Javid

Firstly You shouldn't need a cursor here. Something like the following would have the same semantics (from a starting position where all Event are NULL) and be more efficient.

WITH T
     AS (SELECT [Event],
                RANK() OVER (PARTITION BY [Product], [Customer] 
                                 ORDER BY [Date] DESC) AS Rnk
         FROM   MyTable)
UPDATE T
SET    [Event] = 'No Event'
WHERE  Rnk > 1 

Secondly regarding the question in the title to commit inside a cursor loop is the same as anywhere else. You just need a COMMIT statement. However if you aren't running this inside a larger transaction the UPDATE statement will be auto committed anyway.

Thirdly Your real question doesn't seem to be about commit anyway. It is about the cursor reflecting updates to the data on subsequent iterations. For the case in the question you would need a DYNAMIC cursor

Defines a cursor that reflects all data changes made to the rows in its result set as you scroll around the cursor. The data values, order, and membership of the rows can change on each fetch.

Not all queries support dynamic cursors. The code in the question would but without an ORDER BY it is undeterministic what order the rows would be processed in and thus whether you would see visible results. I have added an ORDER BY and an index to support this to allow a dynamic cursor to be used.

If you try the following you will see the cursor only fetches one row as the dates are processed in descending order and when the first row is processed the table is updated such that no more rows qualify for the next fetch. If you comment out the UPDATE inside the cursor loop all three rows are fetched.

CREATE TABLE MyTable
  (
     [Product]  INT,
     [Customer] INT,
     [Date]     DATETIME,
     [Event]    VARCHAR(10) NULL,
     PRIMARY KEY ([Date], [Product], [Customer])
  )


INSERT INTO MyTable 
VALUES (1,1,'20081201',NULL), 
       (1,1,'20081202',NULL), 
       (1,1,'20081203',NULL)

DECLARE @Product  INT,
        @Customer INT,
        @Date     DATETIME,
        @Event    VARCHAR(10)

DECLARE cur CURSOR DYNAMIC TYPE_WARNING FOR
  SELECT [Product],
         [Customer],
         [Date],
         [Event]
  FROM   MyTable
  WHERE  [Event] IS NULL
  ORDER  BY [Date] DESC

OPEN cur

FETCH NEXT FROM cur INTO @Product, @Customer, @Date, @Event

WHILE @@FETCH_STATUS = 0
  BEGIN
      SELECT @Product,
             @Customer,
             @Date,
             @Event

      -- Now I update my Event value to 'No Event' for records whose date is less than @Date
      UPDATE MyTable
      SET    [Event] = 'No Event'
      WHERE  [Product] = @Product
             AND [Customer] = @Customer
             AND [Date] < @Date

      FETCH NEXT FROM cur INTO @Product, @Customer, @Date, @Event
  END

CLOSE cur

DEALLOCATE cur

DROP TABLE MyTable 

Even if this worked, this would not guarantee the correct result since you miss an ORDER BY clause in your query.

Depending on this, all records, no records or any random subset of records could be updated.

Could you please explain in plain English what your stored procedure should do?

Use Below template

DECLARE @CCount int = 100
    DECLARE @Count int = 0
    DECLARE  @id AS BigInt
    
        
    DECLARE cur Cursor fast_forward for 
        SELECT t1.Id  
             FROM Table1 t1 WITH (NOLOCK) WHERE  <Some where clause>
    OPEN cur
    Begin Tran
    
    While (1=1)
    Begin   
        Fetch next from cur into @id
        If @@Fetch_Status <> 0
            break
    -- do some DML actions
        Delete From Table1 WITH (ROWLOCK) where Id = @id
        Set @count = @count + @@Rowcount
    
        if (@count % @CCount = 0)
        Begin   
            if (@count % 100 = 0)
                Print 'Table1: DML action ' + Cast(@count as Varchar(15)) + ' rows'
    -- for every 100 rows commit tran will trigger , and starts a new one.
            While @@Trancount > 0 Commit Tran
            Begin Tran
        End
    End 
    
    While @@Trancount > 0 Commit Tran
    Close cur
    Deallocate cur