Transact-SQL: scorrere la lista di cursori utilizzando sp_cursor_list

Ecco un esempio della store procedure sp_cursor_list, che restituisce tutte le informazioni relative ai cursori allocati:

DECLARE C_PIPPO CURSOR KEYSET FOR SELECT * FROM VENDITE
OPEN C_PIPPO

DECLARE C_TOPOLINO CURSOR STATIC FOR SELECT * FROM VENDITE
OPEN C_TOPOLINO

DECLARE C_PAPERINO CURSOR DYNAMIC FOR SELECT * FROM VENDITE
OPEN C_PAPERINO

DECLARE C_PLUTO CURSOR FAST_FORWARD FOR SELECT * FROM VENDITE
OPEN C_PLUTO

DECLARE @CURSORI CURSOR

EXEC master.dbo.sp_cursor_list @cursor_return = @CURSORI OUTPUT, @cursor_scope = 2

FETCH NEXT from @CURSORI

WHILE (@@FETCH_STATUS <> -1)
BEGIN

FETCH NEXT from @CURSORI
END

CLOSE @CURSORI
DEALLOCATE @CURSORI

CLOSE C_PAPERINO
DEALLOCATE C_PAPERINO

CLOSE C_TOPOLINO
DEALLOCATE C_TOPOLINO

CLOSE C_PLUTO
DEALLOCATE C_PLUTO

CLOSE C_PIPPO
DEALLOCATE C_PIPPO

Il risultato:

reference_name cursor_name cursor_scope status model concurrency scrollable
open_status cursor_row fetch_status column_count row_count last_operation cursor_handle
C_PIPPO C_PIPPO 2 1 1 1 1
1 16 -9 4 0 1 180150003
C_TOPOLINO C_TOPOLINO 2 1 1 1 1
1 16 -9 4 0 1 180150005
C_PAPERINO C_PAPERINO 2 1 3 3 1
1 -1 -9 4 0 1 180150007
C_PLUTO C_PLUTO 2 1 4 1 0
1 -1 -9 4 0 1 180150009

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>