Verify that the cursor variable contains information with cursor_status('variable', '@Report') : cursor_status « Cursor « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. Cursor
3. cursor_status

22> IF cursor_status('variable', '@Report') != 1
5> CREATE TABLE authors(
6>    au_id          varchar(11),
7>    au_lname       varchar(40)       NOT NULL,
8>    au_fname       varchar(20)       NOT NULL,
9>    phone          char(12)          NOT NULL DEFAULT ('UNKNOWN'),
10>    address        varchar(40)           NULL,
11>    city           varchar(20)           NULL,
12>    state          char(2)               NULL,
13>    zip            char(5)               NULL,
14>    contract       bit               NOT NULL
15> )
16> GO
1> insert authors values('1',  'Joe',   'Abra',   '111 111-1111', '6 St.', 'Berkeley',  'CA', '11111', 1)
2> insert authors values('2',  'Jack',  'Majo',   '222 222-2222', '3 St.', 'Oakland' ,  'CA', '22222', 1)
3> insert authors values('3',  'Pink',  'Cherry', '333 333-3333', '5 Ln.', 'Vancouver', 'BC', '33333', 1)
4> insert authors values('4',  'Blue',  'Albert', '444 444-4444', '7 Av.', 'Vancouver', 'BC', '44444', 1)
5> insert authors values('5',  'Red',   'Anne',   '555 555-5555', '6 Av.', 'Regina',    'SK', '55555', 1)
6> insert authors values('6',  'Black', 'Michel', '666 666-6666', '3 Pl.', 'Regina',    'SK', '66666', 1)
7> insert authors values('7',  'White', 'Sylvia', '777 777-7777', '1 Pl.', 'Rockville', 'MD', '77777', 1)
8> insert authors values('8',  'Yellow','Heather','888 888-8888', '3 Pu',  'Vacaville', 'CA', '88888', 0)
9> insert authors values('9',  'Gold',  'Dep',    '999 999-9999', '5 Av.', 'Oakland',   'CA', '99999', 0)
10> insert authors values('10', 'Siler', 'Dean',   '000 000-0000', '4 Av.', 'Oakland',   'CA', '00000', 1)
11> GO
1>
2>
3> DECLARE authors_cursor CURSOR KEYSET FOR
4> SELECT au_lname
5> FROM authors
6> WHERE au_lname LIKE 'S%'
7>
8> OPEN authors_cursor
9>
10> 
11> 
12> DECLARE @Report CURSOR
13>
14> 
15> EXEC master.dbo.sp_describe_cursor
16>     @cursor_return = @Report OUTPUT,
17>     @cursor_source = 'global',
18>     @cursor_identity = 'authors_cursor'
19>
20> 
21>
22> IF cursor_status('variable', '@Report') != 1
23>     PRINT 'No information available from the cursor'
24>
25> ELSE BEGIN
26> 
27>     WHILE (@@fetch_status = 0)
28>     BEGIN
29>         FETCH NEXT FROM @Report
30>     END
31> END
32>
33> 
34> IF cursor_status('variable',  '@Report') >= -1
35> BEGIN
36>     CLOSE @Report
37>     DEALLOCATE @Report
38> END
39> GO
1>
2> 
3> CLOSE authors_cursor
4> DEALLOCATE authors_cursor
5> GO
1>
2> drop table authors;
3> GO