SQL Server/T-SQL Tutorial/Cursor/DEALLOCATE cursor

DEALLOCATE a cursor

   <source lang="sql">

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></source>

Fetching cursor data into variables and DEALLOCATE cursor

   <source lang="sql">

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</source>