I just wrote a test stored procedure with a cursor inside and I deliberately added in the middle of the cursor body an update that violates a PK in some table.
My test case shows that the cursor goes through 17 times (as expected), and prints 17 error messages (PK violation).
Nevertheless, when I check the result, it is clear that ALL queries both before and after the offending query in ALL 17 runs of the cursor have been executed perfectly.
It is only the single query that is in violation of PK that has failed 17 times.
The question is:
Is this behaviour consistent?
I have a totally different stored procedure that has been sent to all our clients and has crashed in all their databases (PK violation).
I need to send them a clean-up script.
Can I 100% rely on the assumtion that ALL other queries have been successfully executed in all those environments (just like my test case)?
My clean-up strategy highly depends on the answer to this question.
We are all using SQL 2000.