Cursor and stored procedure optimisation

Question

The following stored proc has been written some time ago and now requires modification.

Unable to contact the original developer, I had a look. To me this proc seems over-complicated. Couldn't it be done with a straightforward UPDATE? Can anyone justify the use of CURSOR here?

ALTER PROCEDURE [settle_Stage1]
    @settleBatch int
AS

DECLARE @refDate datetime;
DECLARE @dd int;
DECLARE @uid int;

DECLARE trans_cursor CURSOR FOR 
SELECT uid, refDate FROM tblTransactions WHERE (settle IS NULL ) AND (state IN (  21, 31, 98, 99 ))
OPEN trans_cursor
FETCH FROM trans_cursor INTO @uid, @refDate
WHILE @@FETCH_STATUS = 0
   BEGIN
    SET @dd = DATEDIFF( day, @refDate, getDate())
    IF ( @dd >= '1' )
      BEGIN
        UPDATE tblTransactions
        SET settle = @settleBatch WHERE uid = @uid
      END
    FETCH FROM trans_cursor INTO @uid, @refDate
   END  
CLOSE trans_cursor
DEALLOCATE trans_cursor

Answer 1

If there are triggers involved that would blow up on multiple updated rows, then you would want to iterate. But that would still not justify using an actual CURSOR.

Doing single updates would cause row locks and not page or table locks that a set based update could. Since you're making the transactions smaller, the programmer could have been attempted to remove deadlocks which were caused by a large update.

NOTE: I am not advocating this method, I am only suggesting reasons.