Thursday 28 July 2011

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 is differ from 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

8 comments:

  1. THANKS YOU VERY MUCH FRIEND = DHANYAVAD (IN INDIA-MUMBAI)

    ReplyDelete
  2. Hello I am using cursor with more than 1 table and after opening the cursor i am trying to update the table, but getting Error :- "Msg 258, Level 15, State 1, Line 53, Cannot call methods on nvarchar."

    Can someone please help.

    ReplyDelete
    Replies
    1. Could you please post your code ? Where you are getting this error ?

      Delete
    2. Hi Sanjay,
      Please email me on manjukefernando@gmail.com. I may be able to help you.

      Delete
  3. Very helpful, thank you!

    ReplyDelete
  4. Short and to the point, good job.

    ReplyDelete