2

Is there any way to have a stored procedure automatically throw if any statement fails due to an error?

I'm inside a stored proc with a merge statement which can fail due to a primary key violation, however execution still continues.

Do I have to resort to if @@error != 0 throw ... everywhere?

EDIT: I'm using MS SQL Server 2012

EDIT: This seems to work, but is there a less verbose solution? It seems as if the introduction of try/catch makes flow jump to the catch block when an error is encountered. From there I just rethrow the exception.

begin try    
    ....do lots of sql code
end try
begin catch
    throw;
end catch
CoderBrien
  • 663
  • 1
  • 7
  • 22
  • What *kind* of SQL? Do you mean SQL Server? – RBarryYoung Aug 02 '13 at 21:17
  • One of the great benefits offered by the `MERGE` statement is that you can decide on performing a different action when certain criteria occur (e.g. PK violation). Why do you wish to use a different method? – gvee Aug 02 '13 at 21:39
  • `merge` is just an example. it could be any statement that fails for any reason (eg a bug). – CoderBrien Aug 02 '13 at 21:45

2 Answers2

3

Use SET xact_abort ON at the beginning of the statement. It will cause an automatic rollback if any particular statement fails.

See What is the benefit of using "SET XACT_ABORT ON" in a stored procedure?.

Edit: the above is for SQL-Server.

Community
  • 1
  • 1
Eric Hauenstein
  • 2,557
  • 6
  • 31
  • 41
  • I'm not inside a transaction so I don't think this will work. – CoderBrien Aug 02 '13 at 21:21
  • Add BEGIN TRANSACTION at the start of the SP and COMMIT TRANSACTION at the end. Similar to logixologist's example. – Eric Hauenstein Aug 02 '13 at 21:24
  • That has semantic and performance implications that I don't want. I'm not interested in rolling back the code execution that has successfully completed. I'm looking for semantics similar to C# exceptions. – CoderBrien Aug 02 '13 at 21:25
  • @CoderBrien So, if one row fails on a MERGE, you don't want it to rollback the whole thing - you want it only to throw on the rows that have an error? – Question3CPO Aug 02 '13 at 21:30
  • @Question3CPO: I'm not looking to rollback any changes that have occurred prior to the error, I'm just trying to halt execution. – CoderBrien Aug 02 '13 at 21:37
1

How about wrapping it in a transaction so that if anything fails it will roll back any changes and you can have it return an error message.

Something like

BEGIN Transaction
--Do some code

if @@error > 0 
BEGIN
--Do your throw here and then
ROLLBACK TRANSACTION
END
ELSE

BEGIN
COMMIT TRANSACTION
END
logixologist
  • 3,694
  • 4
  • 28
  • 46
  • I don't understand your code. Is a check for `@@error` required inside a transaction? If so, then what exactly is the purpose of wrapping the code with a transaction? – CoderBrien Aug 02 '13 at 21:24
  • I have always learned it that way. I could be mistaken. The purpose of the transaction is you could have done 100 things AFTER begin transaction. If anything fails in that it will catch it and rollback everything – logixologist Aug 02 '13 at 21:30
  • One option... why not put in your throws in the if @error = and commit the transaction anyway... untested advice btw but it might work – logixologist Aug 02 '13 at 21:32
  • I don't want transaction semantics. I want to retain changes and halt execution at the point of the error. – CoderBrien Aug 02 '13 at 21:38