SQL Server Performance Forum – Threads Archive

Yes more cursor questions ??

Hi I have been able to find this default cursor behaviour in BOL.
So if anyone can take the time to answer these thank you very much:
These questions apply to a stored procedure that uses cursors, this sp may be fired simutanously many times. 1.I presume cursor’s are like temp table , that is a cursor declaration is unique to each connection. If 10 users fire the sp at the same time they wont get a cursor alrady exists error, i am sure they wont but just want to be certain. 2.What happens to a cursor in the following scenario, BEGIN TRAN DECLARE CURSOR
OPEN CURSOR FETCH NEXT FROM CURSOR @@ERROR <> 0
ROLLBACK
RETURN Is the cursor automatically closed and deallocated by the ROLLBACK or disconnection, or either ?
Do i have to worry about closing and deallocating the cursor in every single error part of the transaction ? Thanx

---

1. This is mentioned in BOL! If you don’t specify GLOBAL or LOCAL when declaring a cursor, it depends on the default to local cursor database option. As always, be specific – make it LOCAL to avoid surprises. 2. Always CLOSE and DEALLOCATE, especially if you’re in a loop. AFAIK, you should repeat this before the RETURN instruction, since execution stops at RETURN.

---

Hi Adriaan Thanx again, yes i reckon LOCAL is safe, i do currently deallocate and close cursors in error logic just wanted to make sure i was not wasting my time. Thanx

---

If possible avoid cursor as suggested in your other question Madhivanan Failing to plan is Planning to fail

---

]]>