A cursor created in SQL server. : Declare Cursor « Cursor « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. Cursor
3. Declare Cursor

4> CREATE TABLE Employees(
5> empid   int         NOT NULL,
6> empname varchar(10) NOT NULL,
7> deptno  int         NULL ,
8> jobid   int         ,
9> salary decimal(7,2) NOT NULL
10> )
11> GO
1>
2> INSERT INTO Employees VALUES(1, 'Leo', 400, 30, 3456.00)
3> INSERT INTO Employees VALUES(2, 'Ros', 200, 20, 4325.00)
4> INSERT INTO Employees VALUES(3, 'Chris', 100, 10, 8952.00)
5> INSERT INTO Employees VALUES(4, 'Rob', 400, 30, 1234.00)
6> INSERT INTO Employees VALUES(5, 'Linda', 400, 30, 4567.00)
7> INSERT INTO Employees VALUES(6, 'Lisa', NULL, 30, 8765.00)
8> GO

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)
1>     DECLARE
2>          @empname VARCHAR(40),
3>          @salary MONEY,
4>          @empid integer
5>     DECLARE getemp_curs CURSOR
6>         FOR
7>            SELECT empid, empname, salary FROM employees WHERE empid = 1
8>     OPEN getemp_curs
9>     FETCH NEXT FROM getemp_curs into @empid, @empname, @salary
10>     WHILE @@FETCH_STATUS = 0 BEGIN
11>          UPDATE employees SET salary = @salary WHERE empid = @empid
12>     FETCH NEXT FROM getemp_curs into @empid, @empname, @salary
13>     END
14>     CLOSE getemp_curs
15>     DEALLOCATE getemp_curs
16>     GO

(1 rows affected)
1>
2> drop table Employees;
3> GO