CODEWITHSUNDEEP
sql
CoderBrien
CoderBrien

Reputation: 693

SQL automatically throw if a statement fails?

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

Upvotes: 2

Views: 160

Answers (2)

Eric Hauenstein
Eric Hauenstein

Reputation: 2565

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.

Upvotes: 3

logixologist
logixologist

Reputation: 3834

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

Upvotes: 1

Related Questions