0

I've a bit of a messy situation where an SP #1 calls SP #2 which calls SP #3. I found a bottleneck in #3 under certain conditions which was leading to a timeout so #3 was only completing half its work. I did away with a view that it was using and read directly from the table which reduced execution time drastically and chances have improved that it won't happen again. If however it did happen again, and #3 timesout, I'd like to be able to roll back all the work done in sp 1,2 and 3.

I'm pretty inexperienced with Sql, is this just a case of putting #1 in a transaction which will wrap around everything or does having nested sp's change the approach? If anyone has any good links to what I'm trying to do that would be great

user48408
  • 3,234
  • 11
  • 39
  • 59
  • Also found [this question](http://stackoverflow.com/questions/725891/what-is-the-best-practice-use-of-sql-server-t-sql-error-handling) which may help – Squirrel5853 Nov 15 '13 at 11:53

3 Answers3

0

Im not very experienced with SQL either but my thoughts on how to handle this would be to have a 

BEGIN TRY

END TRY
BEGIN CATCH

END CATCH

and then either THROW the errors back to the calling stored procedure or have an OUT param which determines if the stored procedure successfully completed. Then let the calling procedure decide what they want to do based upon the OUT param.

here is the link for using TRY...CATCH. http://technet.microsoft.com/en-us/library/ms179296(v=sql.105).aspx

Squirrel5853
  • 2,376
  • 1
  • 18
  • 34
0
begin transaction
exec procedure @arg1 = "foo", @arg2 = "bar"

IF @@ERROR <> 0
BEGIN
ROLLBACK
RAISERROR ('Error', 16,1)
RETURN
END 

commit --this will run if no errors

This transaction will encompass all procedures that were called from the main procedure.

DragonZero
  • 810
  • 5
  • 8
  • This approach seems to be the most straight-forward. Interested to see if anyone reading agrees with it. I'm trying to test it at the moment – user48408 Nov 15 '13 at 14:38
0

You can also try to use SET ARITHABORT ON; . See also this.

The solution can be use ARITHABORT + WITH RECOMPILE.

Bogdan Bogdanov
  • 1,707
  • 2
  • 20
  • 31