Dave Hall (1/25/2013)
I'm calling the 1st Proc like before and the transaction errors are gone (yay) BUT the Delete statement still executes and is not rolled back (boo).I thought that if the call to the Proc was within a transaction then it would Rollback everything within (including actions carried out by a called SP)?
Sure it will, but you have nothing that checks to see if the Insert throws an error or not. There's no checks for @@Error after the insert, so no one notices that it threw an error. Hence it looks, to your code, that the inner proc ran fine and hence gets committed.
@@Error - the error code of the previous statement that ran.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability