Fetching cursor data into variables and DEALLOCATE cursor : DEALLOCATE cursor « Cursor « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. Cursor
3. 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