tsql - Advantages on using cursor variable in SQL Server (declare @cn cursor)

Question

In T-SQL a cursor can be declared in two ways (that I know of):

1. declare CursorName cursor for ...
2. declare @CursorName cursor

I was running some tests and I notice that the creation of a cursor variable will not add an entry to the result of sp_cursor_list.

Is there any advantage/disadvantage on using the second approach from the point of view of performance, resource utilization, etc?

PS: I am aware of potential cursor performance issues. I am not asking for a comparison on cursors vs set based. Or cursor vs while with temp/table variable.

Answer 1

There is another advantage to using the DECLARE @local_variable CURSOR syntax that I just discovered.

The advantage occurs when one stored procedure calls another, and both procedures have cursors open at the same time. If DECLARE cursor_name CURSOR is used to define the cursors, and both procedures use the same cursor_name, then you get

Msg 16915: A cursor with the name 'cursor_name' already exists.

On the other hand, If DECLARE @local_variable CURSOR is used to define the cursors in the parent and child stored procedures, then @local_variable is local to each procedure and there is no conflict. For those who haven't used this method before, here is an example, using @C as the local variable:

DECLARE @C AS CURSOR;

SET @C = CURSOR FOR SELECT ...;

OPEN @C;

FETCH NEXT FROM @C INTO ...;

...