TSQL change cursor definition

Question

I'm using a cursor.

DECLARE @column1 NVARCHAR(MAX);

DECLARE cursor_name CURSOR FOR
    SELECT c1
    FROM   table_name;

OPEN cursor_name;
FETCH cursor_name INTO @column1;

WHILE @@FETCH_STATUS = 0
BEGIN

    FETCH cursor_name INTO @column1;
END

CLOSE cursor_name;
DEALLOCATE cursor_name;

Now my question is, can I change the cursor cursor_name's definition after using it? I mean something similar to:

DECLARE cursor_name CURSOR FOR
    SELECT c2
    FROM   table_name2;

Using the same cursor name cursor_name, but the defination is changed. If this is possible, how to do this?

Thanks.

Answer 1

Yes it's possible, but it has to be after your DEALLOCATE it. Have you tried this and it's not working or something?

You may also want to look at whether you really need a cursor. They hurt performance and SQL that uses them can often be rewritten without them.

Take a look at this article which goes over how to do it. They use the less common DECLARE/SET syntax for declaring a cursor (uncommon in my world at least). Here is the example they provide:

USE pubs
GO
DECLARE @MyCursor CURSOR
SET @MyCursor = CURSOR LOCAL SCROLL FOR
SELECT * FROM titles

DEALLOCATE @MyCursor

SET @MyCursor = CURSOR LOCAL SCROLL FOR
SELECT * FROM sales
GO

Answer 2

Avoiding the discussion of whether or not a cursor is necessary, from a purely technical point of view, once the cursor is closed and deallocated, you are free to reuse the name again.