SQL Server/T-SQL Tutorial/Cursor/Fetch cursor

Fetch cursor value with while loop

5> CREATE TABLE Orders (
6>      OrderID int NOT NULL ,
7>      CustomerID nchar (5) NULL ,
8>      EmployeeID int NULL ,
9>      OrderDate datetime NULL ,
10>     RequiredDate datetime NULL ,
11>     ShippedDate datetime NULL ,
12>     ShipVia int NULL ,
13>     Freight money NULL DEFAULT (0),
14>     ShipName nvarchar (40) NULL ,
15>     ShipAddress nvarchar (60) NULL ,
16>     ShipCity nvarchar (15) NULL ,
17>     ShipRegion nvarchar (15) NULL ,
18>     ShipPostalCode nvarchar (10) NULL ,
19>     ShipCountry nvarchar (15) NULL
20> )
21> GO
1>
2>
3> INSERT INTO Orders VALUES (10248,"1",5,"7/4/1996","8/1/2001","7/16/2001",3,32.38,"V","A","R",        NULL,N"51100","France")
4> go
(1 rows affected)
1>
2>
3>    CREATE PROCEDURE spCursorScroll
4>    AS
5>    DECLARE @Counter      int,
6>            @OrderID      int,
7>            @CustomerID   varchar(5)
8>    DECLARE CursorTest cursor
9>    LOCAL
10>    SCROLL
11>    FOR
12>       SELECT OrderID, CustomerID
13>       FROM Orders
14>    SELECT @Counter = 1
15>    OPEN CursorTest
16>    FETCH NEXT FROM CursorTest INTO @OrderID, @CustomerID
17>    PRINT "Row " + CONVERT(varchar,@Counter) + " has an OrderID of " +
18>          CONVERT(varchar,@OrderID) + " and a CustomerID of " + @CustomerID
19>    WHILE (@Counter<=5) AND (@@FETCH_STATUS=0)
20>    BEGIN
21>          SELECT @Counter = @Counter + 1
22>          FETCH NEXT FROM CursorTest INTO @OrderID, @CustomerID
23>          PRINT "Row " + CONVERT(varchar,@Counter) + " has an OrderID of " +
24>          CONVERT(varchar,@OrderID) + " and a CustomerID of " + @CustomerID
25>    END
26>    WHILE (@Counter>1) AND (@@FETCH_STATUS=0)
27>    BEGIN
28>          SELECT @Counter = @Counter - 1
29>          FETCH PRIOR FROM CursorTest INTO @OrderID, @CustomerID
30>          PRINT "Row " + CONVERT(varchar,@Counter) + " has an OrderID of " +
31>          CONVERT(varchar,@OrderID) + " and a CustomerID of " + @CustomerID
32>    END
33>
34>    CLOSE CursorTest
35>    DEALLOCATE CursorTest
36>    GO
1>
2>    drop PROCEDURE spCursorScroll;
3>    GO
1>
2>    drop table Orders;
3>    GO

FETCH NEXT FROM

3>
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

The syntax of the FETCH statement

FETCH [NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n]
FROM [GLOBAL] cursor_name
[INTO @variable_name [, ...]]