http://www.2cto.com/database/201208/146734.html
use TestPublish
go
alter procedure testtransction
AS
begin
begin try
begin transaction
insert into SubTable values (1,2,4);
insert into SubTable values (4,5,1);
commit transaction
select 1 as errornumber
end try
begin catch
select 2 as errornumber
rollback transaction
end catch
end