Benefits of using a cursor variable in SQL Server (declare cursor @cn)

Question

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

• declare CursorName cursor for ...
• declare @CursorName cursor

I did some tests and I noticed that creating a cursor variable would not add entries to the sp_cursor_list result.

Is there an advantage / disadvantage in using the second approach in terms of performance, resource use, etc.

PS: I know about potential cursor performance issues. I do not ask for comparisons on cursors and based on a set. Or cursor vs while with variable temp / table.

Answer 1

From what I read, the purpose of the cursor variable is to use it as an output variable in a stored proc, which allows you to send data to the cursor to another control proc. I have not tried this, so I do not know exactly how this will work, but this is what I get from reading books on the Internet. I would be surprised if there was any measurable difference in performance and, of course, not the improvement that you could get without using the cursor in the first place. If you do not plan to use it as an output variable, I would suggest that staying with a more common cursor definition might make it easier to work with the code.

However, there are very, very few cases where the cursor is actually needed.

Answer 2

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

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

Msg 16915: A cursor named "cursor_name" already exists.

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

 DECLARE @C AS CURSOR; SET @C = CURSOR FOR SELECT ...; OPEN @C; FETCH NEXT FROM @C INTO ...; 

...

Answer 3

I will try to avoid as many cursors as possible (at least if you are thinking about efficiency). Try creating a set-based solution for your problem. They are usually processed much faster than a cursor-based solution.