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