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