Recently while coding at work, a sql server cursor of mine got stuck in an infinite loop. The infinite loop was very surprising to me because I did not expect the result set from the cursor query to change as I updated or inserted new records into the table my cursor query was based on. What I now know is that when a cursor is based on some table, and you update or insert a new record into that table, the cursor will take that data change into consideration. How exactly? Well I'm not sure... (...if you are sure please leave a comment :)
I side-stepped the issue by creating a table variable that I first inserted into, and then spun the cursor off of instead of the spinning directly off of the actual table. Since I am not making any changes to the table variable, but only the actual table, I have not gotten caught in the unending cursor trap.
In addition to the overly aware cursor issue was another problem I have no explanation for. A co-worker noted that one of the fields would sometimes but not always change from Run to Run Complete. Apparently the use of a cursor can induce strange behavior when updating a record that is part of the cursor's result set. In this specific case I was updating the record that the cursor was currently on. Until I have a better grasp of cursors, my use of a table variable seems to side-step this issue by keeping the table my cursor uses and the table I am populating separate.
No comments:
Post a Comment