CODEWITHSUNDEEP
sql-server-2008stored-procedures
GPH
GPH

Reputation: 1897

stored procedure error's but then triggers last section

I have a stored procedure which updates a table, pretty standard insert query and the table in question has a constraint to ensure no duplicate data can go in, it error's just fine when the procedure is executed with duplicate data, however the last step of the proc executes an email stored proc. even when the insert fails the email gets sent? I thought the procedure would stop with the insert error?

Upvotes: 0

Views: 36

Answers (1)

James S
James S

Reputation: 3588

Nope - it depends on the severity of the error. If the severity is less than 11 then by default SQL server will carry on.

I found this article which seems to do a decent job of explaining this: http://blog.sqlauthority.com/2012/04/26/sql-server-introduction-to-sql-error-actions-a-primer/

If I were you I'd make sure that the stored procedure is doing everything inside a transaction with a try/catch block.

EG

BEGIN TRAN
BEGIN TRY
  -- Do insert

  -- send email
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0 ROLLBACK TRAN
END CATCH
IF @@TRANCOUNT > 0 COMMIT TRAN

Upvotes: 1

Related Questions