Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 179
  • Last Modified:

ROLLBACK if @@error > 1

I have a code that does something an I would like to put it into a trasaction and if it give me an error then I would like to do a ROLLBACK and send an email.

Coul you recommend me the best way of doing it in SQL 2005

Thank you in advance
0
amedexitt
Asked:
amedexitt
1 Solution
 
Chris LuttrellSenior Database ArchitectCommented:
you want something like this:
Create Procedure yourProcedure
AS
BEGIN TRY
	BEGIN Transaction;
 
	-- Put your code here
 
	COMMIT Transaction;
END TRY
BEGIN CATCH
	IF @@TRANCOUNT > 0
		ROLLBACK;
 
	EXEC msdb.dbo.sp_send_dbmail
@recipients=N'user@domainl.com',@body='Message Body', 
@subject ='Message Subject'
 
END CATCH

Open in new window

0
 
amedexittAuthor Commented:
Perfect
Thank you very much
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now