Can I limit blocking to individual loops within my SQL cursor

Question

Hi Folks,

I've some work to do on a largish database which basically requires calling a stored proc with different parameters for every value in a table (approx. 20k entries). I want to do this without blocking on that table or the other tables involved for each loop for the time it takes the whole process to run. Is is possible from within SQL to limit the transactions to individual loops over the cursor?

It'll be pretty simple for me to check manually if there were changes to the table I'm looping over after the process has completed and run it again for the new entries so I'm not concerned about tracking changes to that.

Answer 1

You can set the transaction isolation level to read uncommited. but be sure that's what you really want, since you can get dirty reads, lost updates, phantom updates, etc....

Answer 2

I would suggest that looping through 20000 records is a bad choice. Better to replace the stored proc with a set-based solution. YOu should avoid using cursors.