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.
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
good one, keep the good work on
ReplyDeleteThank you the code was very useful
ReplyDeleteTHANKS YOU VERY MUCH FRIEND = DHANYAVAD (IN INDIA-MUMBAI)
ReplyDeleteHello 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."
ReplyDeleteCan someone please help.
Could you please post your code ? Where you are getting this error ?
DeleteHi Sanjay,
DeletePlease email me on manjukefernando@gmail.com. I may be able to help you.
Very helpful, thank you!
ReplyDeleteShort and to the point, good job.
ReplyDelete