It seems like my transaction is partially saved. Are there any circumstances where a transaction is partially saved or am i missing something with in my stored procedure here is what i used
@p1 is my insert & update statements in a string format divided by semicolon
The transactions should be committed or rolledback in full. That is the basics of transactions, but there could be some situations when the error is not trapped by the catch statement and thrown to the caller, depending on the error (and SQL server version?).
If the caller (the application or another stored procedure) doesn't trap the error and make a commit then you could get this kind of situation.
Are you using two different statements in EXEC? Are you sure that the second statement is executed at all? Have you tried to validate the two different statements independent of each other? Use a print to display the code (@p1) and see if it is correct.
I use dynamic SQL with caution for a couple of reasons:
answered Feb 17 '10 at 04:18 AM