CODEWITHSUNDEEP
sql-servert-sqldatabase-cursor
Just a learner
Just a learner

Reputation: 28572

TSQL change cursor definition

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.

Upvotes: 11

Views: 10105

Answers (2)

Abe Miessler
Abe Miessler

Reputation: 85046

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

Upvotes: 12

Joe Stefanelli
Joe Stefanelli

Reputation: 135799

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.

Upvotes: 5

Related Questions