DECLARE cursor LOCAL : Declare Cursor « Cursor « SQL Server / T-SQL Tutorial

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

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