Cursor Differences

• Gaurav Srivastava

  Mr or Mrs. 500

  Points: 571

  More actions

  October 2, 2016 at 6:13 am

  #321860

  I have a table named "Pdsze", i alter table and add new column en_id. in which i have to update en_id field values in each records, the table contains around 10000 records already,

  below i mentioned 3 methods as per my knowledge to this task, but i am little bit confused what are differences in between all these and which one is better to do this task. 1. Update cursor 2. A Normal cursor 3. Through while loop, even we can do the same work (mentioned below) by all of this methods.

  so i use CURSOR

  1.

  DECLARE @pdid int,@szeid smallint,@enid int,@menid int=13122

  DECLARE Cur1 CURSOR LOCAL

  FOR SELECT Pdsze.pd_id,Pdsze.sze_id FROM Pdsze

  FOR UPDATE OF Pdsze.en_id

  OPEN Cur1

  FETCH NEXT FROM Cur1

  INTO @pdid,@szeid

  WHILE (@@FETCH_STATUS=0)

  BEGIN

  SET @menid+=1

  UPDATE Pdsze SET pdsze.en_id=@menid WHERE CURRENT OF Cur1

  FETCH NEXT FROM Cur1

  INTO @pdid,@szeid

  END

  CLOSE Cur1

  DEALLOCATE Cur1

  2.

  DECLARE @pdid int,@szeid smallint,@enid int,@menid int=13122

  DECLARE Cur1 CURSOR LOCAL

  FOR SELECT Pdsze.pd_id,Pdsze.sze_id FROM Pdsze

  OPEN Cur1

  FETCH NEXT FROM Cur1

  INTO @pdid,@szeid

  WHILE (@@FETCH_STATUS=0)

  BEGIN

  SET @menid+=1

  UPDATE Pdsze SET pdsze.en_id=@menid WHERE Pdsze.pd_id=@pdid AND Pdsze.sze_id=@szeid

  FETCH NEXT FROM Cur1

  INTO @pdid,@szeid

  END

  CLOSE Cur1

  DEALLOCATE Cur1

  3. By While Loop,

  please suggest which one is better and what are differences between them.

  it might be very cheap question in some experts vision, but please give answer.