SQL Server/T-SQL Tutorial/Cursor/DEALLOCATE cursor
DEALLOCATE a cursor
4> USE master
5> GO
Changed database context to "master".
1>
2> CREATE PROC sp_cursor_info
3> (@scope int = 3)
4> AS
5> DECLARE @Report CURSOR
6> EXEC master.dbo.sp_cursor_list @cursor_return = @Report OUTPUT,
7>
8> @cursor_scope = @scope
9> FETCH NEXT FROM @Report
10> WHILE (@@FETCH_STATUS = 0)
11> BEGIN
12> FETCH NEXT FROM @Report
13> END
14> CLOSE @Report
15> DEALLOCATE @Report
16> RETURN
17> GO
1>
2>
Fetching cursor data into variables and DEALLOCATE cursor
4>
5> CREATE TABLE titles(
6> title_id varchar(20),
7> title varchar(80) NOT NULL,
8> type char(12) NOT NULL,
9> pub_id char(4) NULL,
10> price money NULL,
11> advance money NULL,
12> royalty int NULL,
13> ytd_sales int NULL,
14> notes varchar(200) NULL,
15> pubdate datetime NOT NULL
16> )
17> GO
1>
2> insert titles values ("1", "Secrets", "popular_comp", "1389", $20.00, $8000.00, 10, 4095,"Note 1","06/12/94")
3> insert titles values ("2", "The", "business", "1389", $19.99, $5000.00, 10, 4095,"Note 2","06/12/91")
4> insert titles values ("3", "Emotional", "psychology", "0736", $7.99, $4000.00, 10, 3336,"Note 3","06/12/91")
5> insert titles values ("4", "Prolonged", "psychology", "0736", $19.99, $2000.00, 10, 4072,"Note 4","06/12/91")
6> insert titles values ("5", "With", "business", "1389", $11.95, $5000.00, 10, 3876,"Note 5","06/09/91")
7> insert titles values ("6", "Valley", "mod_cook", "0877", $19.99, $0.00, 12, 2032,"Note 6","06/09/91")
8> insert titles values ("7", "Any?", "trad_cook", "0877", $14.99, $8000.00, 10, 4095,"Note 7","06/12/91")
9> insert titles values ("8", "Fifty", "trad_cook", "0877", $11.95, $4000.00, 14, 1509,"Note 8","06/12/91")
10> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1> CREATE TABLE sales(
2> stor_id char(4) NOT NULL,
3> ord_num varchar(20) NOT NULL,
4> ord_date datetime NOT NULL,
5> qty smallint NOT NULL,
6> payterms varchar(12) NOT NULL,
7> title_id varchar(80)
8> )
9> GO
1> insert sales values("1", "QA7442.3", "09/13/94", 75, "ON Billing","1")
2> insert sales values("2", "D4482", "09/14/94", 10, "Net 60", "1")
3> insert sales values("3", "N914008", "09/14/94", 20, "Net 30", "2")
4> insert sales values("4", "N914014", "09/14/94", 25, "Net 30", "3")
5> insert sales values("5", "423LL922", "09/14/94", 15, "ON Billing","3")
6> insert sales values("6", "423LL930", "09/14/94", 10, "ON Billing","2")
7> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> DECLARE crTitleSales CURSOR FOR
4> SELECT title, qty
5> FROM titles t
6> JOIN sales s ON t.title_id = s.title_id
7> WHERE qty > 10
8> DECLARE @chvTitle varchar(100),
9> @intQty smallint
10> OPEN crTitleSales
11> FETCH NEXT FROM crTitleSales INTO @chvTitle, @intQty
12> CLOSE crTitleSales
13> DEALLOCATE crTitleSales
14> PRINT "First Title with sales quantity over 10"
15> SELECT @chvTitle AS "Title",
16> @intQty AS "Quantity"
17> GO
First Title with sales quantity over 10
Title Quantity
---------------------------------------------------------------------------------------------------- --------
Secrets 75
(1 rows affected)
1>
2> drop table titles;
3> drop table sales;
4> GO