Click here to Skip to main content
Click here to Skip to main content

How to Use Update Cursors in SQL Server

, 28 Jul 2011
Rate this:
Please Sign up or sign in to vote.
Using 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.

img_scr_001_a

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Manjuke Fernando
Technical Lead Air Liquide Industrial Services (Singapore)
Singapore Singapore
I have been in software industry for more than 8 years. I have developed different type of software using different languages. Many of them are database related (both web & window based), SQL being as the back end most of the time. Up-to-date I have knowledge in languages such as C#, VB.Net, T-SQL, JAVA, VB6 & C++, making C# the most proficient of all. Also I have worked using different technologies like ASP.Net, SharePoint, Crystal Reports (But I really hate designing reports) & MS SQL Server and have involved in designing & developing software for major companies like FedEx, Softlogic Holdings, IronOne Technologies & Brandix. Currently I am working as a Tech Lead in Singapore.
Follow on   Twitter   LinkedIn

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 28 Jul 2011
Article Copyright 2011 by Manjuke Fernando
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid