Defining a TSQL Change Pointer

Question

I am 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 definition of cursor cursor_name 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 protection will be changed. If possible, how to do it?

Thanks.

Answer 1

Yes, perhaps, but it should be after your DEALLOCATE. Have you tried this and it is not working or something else?

You can also see if you really need a cursor. They hurt performance, and the SQL that uses them can often be rewritten without them.

Take a look at this article that tells you how to do this. They use the less common DECLARE / SET syntax to declare a cursor (at least in my world). Here is an example that 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 the need or absence of the cursor, from a purely technical point of view, once the cursor is closed and released, you can reuse this name again.

Answer 3

Correction to Abe Miessler misleading comments about cursor performance:

Transact-SQL cursors are extremely efficient at storing procedures and triggers. This is because everything is compiled into a single execution plan on the server and there is no network traffic associated with extracting strings

• Extract msdn article from link.

What is bad for performance, repeatedly calls the global cursor for the next line due to the stored procedure. It is better to use the api functions for batch processing the client to reduce db rounds.

You can use Transact-SQL cursors in applications using FETCH and bind each column returned by FETCH to a program variable. However, the Transact-SQL FETCH does not support batches, so this is the least efficient way to return data to the application. Getting each line requires a return to the server. It’s more efficient to use the cursor functionality built into the database APIs that support row row fetching.