Local scroll cursor : Declare Cursor « Cursor « SQL Server / T-SQL Tutorial

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

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