How to Use Update Cursors in SQL Server

There can be a situation where you have to use a cursor, even though the experts say not to use cursors or to avoid them as much as possible. But if you look closely, most of the time we use cursors to iterate through a row collection and update the same table.

In these type of situations, it is ideal to use a Update Cursor, than using the default read only one.

Consider the following table:

CREATE TABLE [dbo].[SAMPLE_EMPLOYEE](
    [EMP_ID] [int] NOT NULL,
    [RANDOM_GEN_NO] [VARCHAR](50) NULL
) ON [PRIMARY]

Insert few records to the above table using the following script:

SET NOCOUNT ON
DECLARE @REC_ID        AS INT

SET @REC_ID = 1

WHILE (@REC_ID <= 1000)
BEGIN
    INSERT INTO SAMPLE_EMPLOYEE
    SELECT @REC_ID,NULL
    
    IF(@REC_ID <= 1000)
    BEGIN
        SET @REC_ID = @REC_ID + 1
        CONTINUE
    END
    
    ELSE
    BEGIN
        BREAK
    END
END
SET NOCOUNT OFF

Next, we will add a Primary Key using the below script (or you can use the table designer):

ALTER TABLE [dbo].[SAMPLE_EMPLOYEE] _
ADD CONSTRAINT [PK_SAMPLE_EMPLOYEE] PRIMARY KEY CLUSTERED 
(
    [EMP_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, _
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF,_ 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

** Please note: A primary key should be there if we are to use an update cursor. Otherwise the cursor will be read only.

Here is how you use the Update Cursor. I have highlighted the areas which differ compared with a normal cursor. You have to mention which column you are going to update (or all columns in your selection will be updatable) and you have to use ‘where current of <cursor>’ in your update statement.

SET NOCOUNT ON
DECLARE 
    @EMP_ID                        AS INT, 
    @RANDOM_GEN_NO    AS VARCHAR(50),
    @TEMP                        AS VARCHAR(50)

DECLARE EMP_CURSOR CURSOR FOR
SELECT EMP_ID, RANDOM_GEN_NO FROM SAMPLE_EMPLOYEE FOR UPDATE OF RANDOM_GEN_NO
OPEN EMP_CURSOR
FETCH NEXT FROM EMP_CURSOR
INTO @EMP_ID, @RANDOM_GEN_NO

WHILE (@@FETCH_STATUS = 0)
BEGIN
    SELECT @TEMP =  FLOOR(RAND()*10000000000000)
    UPDATE SAMPLE_EMPLOYEE SET RANDOM_GEN_NO = @TEMP WHERE CURRENT OF EMP_CURSOR
    
    FETCH NEXT FROM EMP_CURSOR
    INTO @EMP_ID, @RANDOM_GEN_NO
END

CLOSE EMP_CURSOR
DEALLOCATE EMP_CURSOR

SET NOCOUNT OFF