复制代码 代码如下:
–方式一
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]