SQL Server/T-SQL/Cursor/Open cursor
A SQL script that declares and uses a cursor
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>
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