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