OPEN a cursor
6> CREATE TABLE Orders (
7> OrderID int NOT NULL ,
8> CustomerID nchar (5) NULL ,
9> EmployeeID int NULL ,
10> OrderDate datetime NULL ,
11> RequiredDate datetime NULL ,
12> ShippedDate datetime NULL ,
13> ShipVia int NULL ,
14> Freight money NULL DEFAULT (0),
15> ShipName nvarchar (40) NULL ,
16> ShipAddress nvarchar (60) NULL ,
17> ShipCity nvarchar (15) NULL ,
18> ShipRegion nvarchar (15) NULL ,
19> ShipPostalCode nvarchar (10) NULL ,
20> ShipCountry nvarchar (15) NULL
21> )
22> GO
1> CREATE PROCEDURE spCursorScope
2> AS
3> DECLARE @Counter int,
4> @OrderID int,
5> @CustomerID varchar(5)
6>
7> DECLARE CursorTest CURSOR
8> GLOBAL
9> FOR
10> SELECT OrderID, CustomerID
11> FROM Orders
12>
13> SELECT @Counter = 1
14> OPEN CursorTest
15> FETCH NEXT FROM CursorTest INTO @OrderID, @CustomerID
16> PRINT 'Row ' + CONVERT(varchar,@Counter) + ' has an OrderID of ' +
17> CONVERT(varchar,@OrderID) + ' and a CustomerID of ' + @CustomerID
18> WHILE (@Counter<=5) AND (@@FETCH_STATUS=0)
19> BEGIN
20> SELECT @Counter = @Counter + 1
21> FETCH NEXT FROM CursorTest INTO @OrderID, @CustomerID
22> PRINT 'Row ' + CONVERT(varchar,@Counter) + ' has an OrderID of ' +
23> CONVERT(varchar,@OrderID) + ' and a CustomerID of ' + @CustomerID
24> END
25> GO
1>
2> drop PROCEDURE spCursorScope;
3> GO
1>
2> drop table Orders;
3> GO
Related examples in the same category