DECLARE CURSOR GLOBAL FOR : Declare Cursor « Cursor « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. Cursor
3. Declare 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