SQL Server 存储过程事务模板

CREATE TABLE [dbo].[test](
[aa] [nvarchar](10) NULL,
[bb] [nvarchar](3) NULL
)

 

IF OBJECTPROPERTY(OBJECT_ID(N'dbo.sp_test'), N'IsProcedure') = 1 DROP PROC dbo.sp_test
GO
CREATE PROC dbo.sp_test
AS
BEGIN
SET NOCOUNT ON;
Set XACT_ABORT ON;
begin try
begin tran
insert into [test]
select '1','225'

insert into [test]
select '1','2255555555'

 

update [test] set aa='222'

commit tran
end try
begin catch
--DECLARE @ErrorMessage NVARCHAR(4000);
-- DECLARE @ErrorSeverity INT;
-- DECLARE @ErrorState INT;
if xact_state()=-1
rollback tran;
-- SELECT
-- @ErrorMessage = ERROR_MESSAGE(),
-- @ErrorSeverity = ERROR_SEVERITY(),
-- @ErrorState = ERROR_STATE();

--RAISERROR (@ErrorMessage, -- Message text.
-- @ErrorSeverity, -- Severity.
-- @ErrorState -- State.
-- );

;THROW
end catch

SET NOCOUNT off;
END

exec sp_test

select * from [test]
truncate table [test]

转载于:https://www.cnblogs.com/fanxin2015/p/10755506.html