Sqlserver 存储过程中结合事务的代码

--方式一
if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[usp_procedurewithtransaction_demo]') and objectproperty(id, n'isprocedure') = 1)
drop procedure [dbo].[usp_procedurewithtransaction_demo]
go
-- =============================================
-- author: <chengxiaoming>
-- create date: <2010-06-11>
-- description: <demo:存储过程中使用事务>
-- =============================================
create procedure [dbo].[usp_procedurewithtransaction_demo]
as
begin
set xact_abort on
begin transaction
insert into lock(locktypeid) values('a')--此语句将出错，locktypeid为int类型
update lock set locktypeid = 2 where lockid = 32
commit transaction
set xact_abort off
end
go

--方式二
if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[usp_procedurewithtransaction_demo]') and objectproperty(id, n'isprocedure') = 1)
drop procedure [dbo].[usp_procedurewithtransaction_demo]
go
-- =============================================
-- author: <chengxiaoming>
-- create date: <2010-06-11>
-- description: <demo:存储过程中使用事务>
-- =============================================
create procedure [dbo].[usp_procedurewithtransaction_demo]
as
begin
begin transaction
insert into lock(locktypeid) values('a')--此语句将出错，locktypeid为int类型
update lock set locktypeid = 1 where lockid = 32
commit transaction
if(@@error <> 0)
rollback transaction
end
go

--方式三
if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[usp_procedurewithtransaction_demo]') and objectproperty(id, n'isprocedure') = 1)
drop procedure [dbo].[usp_procedurewithtransaction_demo]
go
-- =============================================
-- author: <chengxiaoming>
-- create date: <2010-06-11>
-- description: <demo:存储过程中使用事务>
-- =============================================
create procedure [dbo].[usp_procedurewithtransaction_demo]
as
begin
begin try
begin transaction
update lock set locktypeid = 1 where lockid = 32--此语句将出错，locktypeid为int类型
insert into lock(locktypeid) values('a')
commit transaction
end try
begin catch
rollback transaction
end catch
end
go

exec [usp_procedurewithtransaction_demo]