How to use cursor to update record

Question

i am using below query to update my all records but it starts update from second row how can i modify it to do changes from 1'st row ?

i am using mssql 2008

i think i can not use @@FETCH_STATUS as 1'st line as it is global.

thanks in advance

use vivdb

DECLARE @empno as int;
select @empno = 10;

DECLARE Employee_Cursor CURSOR FOR select * from emp
OPEN Employee_Cursor;

FETCH NEXT from Employee_Cursor

WHILE @@FETCH_STATUS = 0
   BEGIN

      update emp set empno = @empno;
      select @empno = @empno+1;

     FETCH NEXT from Employee_Cursor
   END;

CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor;
GO

Answer 1

It looks like you want to assign an incremented value to empno starting with 10.

You can use a CTE and row_number() to do that. No need for a cursor.

;with C as
(
  select empno,
         9 + row_number() over(order by (select 1)) as NewEmpNo
  from emp       
)
update C
set empno = NewEmpNo

You cursor version could look like this to do the same.

DECLARE @empno AS INT;
DECLARE @CurEmpNo AS INT;

SELECT @empno = 10;

DECLARE employee_cursor CURSOR FOR
  SELECT empno
  FROM   emp

OPEN employee_cursor;

FETCH NEXT FROM employee_cursor INTO @CurEmpNo

WHILE @@FETCH_STATUS = 0
  BEGIN
      UPDATE emp
      SET    empno = @empno
      WHERE  CURRENT OF employee_cursor;

      SELECT @empno = @empno + 1;

      FETCH NEXT FROM employee_cursor INTO @CurEmpNo
  END;

CLOSE employee_cursor;

DEALLOCATE employee_cursor;  

Answer 2

Cursors are generally not a good solution, so any solution such as @Mikael Eriksson's is potentially better. However if you really have to use a cursor to do an update then you should mark it as insensitive:

DECLARE Employee_Cursor INSENSITIVE CURSOR FOR
SELECT empno FROM emp

I haven't confirmed this on 2008 but certainly on 2005 and below, you can expect all kinds of weirdness if you update the data your cursor is defined against, without marking the cursor as insensitive.

Another option could be to use a temporary table.