How to Rollback Transaction in Try…Catch Block within a Stored Procedure

Syntax:

GO
set xact_abort on

declare @r table
(
em varchar(100)
, ed datetime
)

begin tran

BEGIN TRY

DECLARE @MyInt INT;
-- Following statement will create Devide by Zero Error
SET @MyInt = 1/0;

commit

END TRY

BEGIN CATCH
insert into @r(em, ed)
SELECT 'Divide by zero error encountered.', getdate()
rollback
END CATCH;

select * from @r

GO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s