Mega Code Archive
Fetch cursor value with while loop
<![CDATA[
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
]]>