Cursors in SQL Server 2005 database

Question

I am working with cursors and successfully executed in T-SQL and the database I use is Microsoft SQL Server 2005.

My query is after I execute the cursor, the output is shown in message area. When I deallocate the cursor using deallocate <cursor name> it gets deallocated. Again I execute my cursor. Now in the message area I get this:

"Command(s) successfully completed "

and I am not getting the output of the cursor. To resolve, I copied the cursor code and opened a new query area and pasted there. It works perfect for the first time. When I rerun the cursor, the same message is displayed in the message area.

How do I re-execute the same cursor code in the same window rather than creating a new SQL window?

Answer 1

Without seeing your code it is hard to say what is wrong, but I do know that cursors are code of last resort and can usually be done with less code and faster performance by using another method. See this link for ideas of better ways to handle your task:

http://wiki.lessthandot.com/index.php/Cursors_and_How_to_Avoid_Them

Answer 2

You are changing state with your execution. What that means, I cannot tell w/o actually seeing your code. But your database and session are in one state when you execute the code first time, and different state when you execute it again. It could be something as simple as unbalanced transactions. The second time, in the new state, the cursor doesn't select anything perhaps, or your code takes a completely different IF branch and doesn't execute any cursor. W/o code, is impossible to tell exactly what is the difference in the database/session state.