ALTER
PROCEDURE
[
dbo
]
.
[
test
]
@from varchar ( 50 ),
@to varchar ( 50 ),
@num int
AS
BEGIN TRY
BEGIN TRAN ;
update vc set balance = balance - @num where [ name ] = @from ;
update vc set balance = balance + @num where [ name ] = @to ;
COMMIT TRAN ;
END TRY
BEGIN CATCH
ROLLBACK TRAN
EXEC PE_THROW;
END CATCH
@from varchar ( 50 ),
@to varchar ( 50 ),
@num int
AS
BEGIN TRY
BEGIN TRAN ;
update vc set balance = balance - @num where [ name ] = @from ;
update vc set balance = balance + @num where [ name ] = @to ;
COMMIT TRAN ;
END TRY
BEGIN CATCH
ROLLBACK TRAN
EXEC PE_THROW;
END CATCH
自定义的pe_throw存储过程如下:
set
ANSI_NULLS
ON
set QUOTED_IDENTIFIER ON
GO
/* ****************************************************************
-- 过程名:PE_THROW
-- 输 入:
-- 输 出:
-- 抛出异常
-- 功能描述: 接收调用程序的异常并抛给上一级程序. 注意每个数据库都应包含该过程.
-- 调用模块: 所有包含事务控制的过程
-- 操作表
-- 作 者:
-- 日 期: 2007-01-30
-- 修 改:
-- 日 期:
-- 版本
*************************************************************** */
ALTER PROCEDURE [ dbo ] . [ PE_THROW ]
AS
BEGIN
SET NOCOUNT ON ;
DECLARE
@ErrorMessage NVARCHAR ( 4000 ),
@ErrorNumber INT ,
@ErrorSeverity INT ,
@ErrorState INT ,
@ErrorLine INT ,
@ErrorProcedure NVARCHAR ( 200 );
SELECT
@ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE(),
@ErrorProcedure = ISNULL (ERROR_PROCEDURE(), ' - ' );
SELECT @ErrorMessage = N ' Error %d, Level %d, State %d, Procedure %s, Line %d, ' + ' Message: ' + ERROR_MESSAGE();
RAISERROR
(
@ErrorMessage ,
@ErrorSeverity ,
1 ,
@ErrorNumber ,
@ErrorSeverity ,
@ErrorState ,
@ErrorProcedure ,
@ErrorLine
);
END
set QUOTED_IDENTIFIER ON
GO
/* ****************************************************************
-- 过程名:PE_THROW
-- 输 入:
-- 输 出:
-- 抛出异常
-- 功能描述: 接收调用程序的异常并抛给上一级程序. 注意每个数据库都应包含该过程.
-- 调用模块: 所有包含事务控制的过程
-- 操作表
-- 作 者:
-- 日 期: 2007-01-30
-- 修 改:
-- 日 期:
-- 版本
*************************************************************** */
ALTER PROCEDURE [ dbo ] . [ PE_THROW ]
AS
BEGIN
SET NOCOUNT ON ;
DECLARE
@ErrorMessage NVARCHAR ( 4000 ),
@ErrorNumber INT ,
@ErrorSeverity INT ,
@ErrorState INT ,
@ErrorLine INT ,
@ErrorProcedure NVARCHAR ( 200 );
SELECT
@ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE(),
@ErrorProcedure = ISNULL (ERROR_PROCEDURE(), ' - ' );
SELECT @ErrorMessage = N ' Error %d, Level %d, State %d, Procedure %s, Line %d, ' + ' Message: ' + ERROR_MESSAGE();
RAISERROR
(
@ErrorMessage ,
@ErrorSeverity ,
1 ,
@ErrorNumber ,
@ErrorSeverity ,
@ErrorState ,
@ErrorProcedure ,
@ErrorLine
);
END