SQL Server/T-SQL Tutorial/System Functions/sp describe cursor

Execute sp_describe_cursor into the cursor variable

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