SQL Server/T-SQL/Cursor/Open cursor
A SQL script that declares and uses a cursor
<source lang="sql">
18> create table Bankers( 19> BankerID Integer, 20> BankerName VARCHAR(20), 21> BankerContactLName VARCHAR(20), 22> BankerContactFName VARCHAR(20), 23> BankerCity VARCHAR(20), 24> BankerState VARCHAR(20), 25> BankerZipCode VARCHAR(20), 26> BankerPhone VARCHAR(20) 27> ) 28> GO 1> 2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111"); 3> GO (1 rows affected) 1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222"); 2> GO (1 rows affected) 1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333"); 2> GO (1 rows affected) 1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444"); 2> GO (1 rows affected) 1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555"); 2> GO (1 rows affected) 1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666"); 2> GO (1 rows affected) 1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777"); 2> GO (1 rows affected) 1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888"); 2> GO (1 rows affected) 1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999"); 2> GO (1 rows affected) 1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000"); 2> GO (1 rows affected) 1> 2> DECLARE Bankers_Cursor CURSOR 3> STATIC 4> FOR 5> SELECT BankerID, BankerName 6> FROM Bankers 7> ORDER BY BankerName 8> OPEN Bankers_Cursor 9> FETCH NEXT FROM Bankers_Cursor 10> WHILE @@FETCH_STATUS = 0 11> FETCH NEXT FROM Bankers_Cursor 12> CLOSE Bankers_Cursor 13> DEALLOCATE Bankers_Cursor 14> GO BankerID BankerName
--------------------
1 ABC Inc.
(1 rows affected) BankerID BankerName
--------------------
2 DEF Inc.
(1 rows affected) BankerID BankerName
--------------------
9 DFG Inc.
(1 rows affected) BankerID BankerName
--------------------
0 GHJ Inc.
(1 rows affected) BankerID BankerName
--------------------
3 HJI Inc.
(1 rows affected) BankerID BankerName
--------------------
7 OIP Inc.
(1 rows affected) BankerID BankerName
--------------------
4 QWE Inc.
(1 rows affected) BankerID BankerName
--------------------
5 RTY Inc.
(1 rows affected) BankerID BankerName
--------------------
8 SAD Inc.
(1 rows affected) BankerID BankerName
--------------------
6 YUI Inc.
(1 rows affected) BankerID BankerName
--------------------
(0 rows affected) 1> 2> 3> drop table Bankers; 4> GO 1>
</source>
OPEN a cursor
<source lang="sql">
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
</source>
