Cursor in t-SQL

be0 · Oct 27, 2008

Hi Fellas,

I have this function in T-SQL SQL Server 2005. Now I created this cursor in it. There is one element within the while loop that once result is found it exits the while loop and calls the return result statement.

I forgot to include the close cursor and deallocate cursor statements here.

Just out of curiosity what happens to this cursor everytime i exit this loop?

PC-XT · Oct 28, 2008

Here is a quote from http://techahead.wordpress.com/

When you are done using a cursor, don’t just CLOSE it, you must also DEALLOCATE it. Deallocation is required to free up the SQL Server resources used by the cursor. If you only CLOSE the cursor, locks are freed, but SQL Server resources are not. If you don’t DEALLOCATE your cursors, the resources used by the cursor will stay allocated, degrading the performance of your server until they are released.
Click to expand...

I think once the server is restarted, any degraded resources are put right again.

If you don't close it, you may not be able to open it again.

Log in or Sign up

Cursor in t-SQL

be0 Corporal

PC-XT Master Sergeant