April 15, 2015 at 11:53 am
Hi,
I'm a bit puzzled. I have a stored procedure with a cursor and while loop. I have two update statements that update the same table. If I comment out Update Statement A, it runs Update Statement B without any issue. If I comment out Update Statement B, Update Statement A will keep updating the same record in an infinite loop.
I guess I don't understand why two, nearly identical update statements would behave so differently inside of a cursor. If I run them individually outside of the cursor/sproc, they both work fine.
The only difference is which field is getting updated. Update Statement A is updating a field that is a foreign key to another table. But if that is an issue, shouldn't this update statement fail when run outside of the loop as well?
Any suggestions would be greatly appreciated!
Thanks.
April 15, 2015 at 11:57 am
tarr94 (4/15/2015)
Hi,I'm a bit puzzled. I have a stored procedure with a cursor and while loop. I have two update statements that update the same table. If I comment out Update Statement A, it runs Update Statement B without any issue. If I comment out Update Statement B, Update Statement A will keep updating the same record in an infinite loop.
I guess I don't understand why two, nearly identical update statements would behave so differently inside of a cursor. If I run them individually outside of the cursor/sproc, they both work fine.
The only difference is which field is getting updated. Update Statement A is updating a field that is a foreign key to another table. But if that is an issue, shouldn't this update statement fail when run outside of the loop as well?
Any suggestions would be greatly appreciated!
Thanks.
Quick suggestion, post the ddl and the code with some sample data
😎
Edit: Typo
April 15, 2015 at 1:46 pm
This is a curious one. I'm wondering if the cursor is dynamic and the loop is contingent on the column being updated. Are there any triggers on the table being updated?
Like Eirikur said, please post the DDL and sample data for the table and the procedure giving you the problem. If there's anything else about the table we need to know (triggers, etc.) please include that too.
My real hope is that we can help you get rid of the cursor altogether.
Edit: Added question about triggers.
April 15, 2015 at 2:16 pm
Removing the cursor and while loop really should be the first step here. Given that you have two updates that update the same row can you just combine them into a single update statement?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 15, 2015 at 3:25 pm
Thank you everyone for taking the time to respond.
Our issue seems to be related to a clustered index on the field. We're moving away from the cursor/loop solution towards a more traditional update.
Still not sure why the update behaved differently inside the cursor than outside of it...
Thanks again!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply