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 ...;

...

Answer 2

From what I read the purpose of the cursor variable is to be able to use it as an output variable in stored proc, thus enabling you to send the data in the cursor to another controlling proc. I have not tried this so I don't know exactly how it would work, but that is what I get from reading Books Online. I would be surprised if there is any measurable performance difference and certainly not the the improvement you could get by not using a cursor in the first place. If you aren't planning to use it as an output variable, I'd suggest that staying with the more common cursor definiton might make the code easier to maintain.

That said, there are very, very few cases where a cursor is actually needed.