--存储过程中使用事务,并且加入异常处理机制.
-- =============================================
CREATE PROCEDURE [dbo].[UP_Orders_Import]
AS
BEGIN
BEGIN TRAN --开启事务
BEGIN TRY
SELECT 1/0
COMMIT TRAN --提交事务
END TRY
BEGIN CATCH
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, -- parameter: original error number.
@ErrorSeverity, -- parameter: original error severity.
@ErrorState, -- parameter: original error state.
@ErrorProcedure, -- parameter: original error procedure name.
@ErrorLine -- parameter: original error line number.
) ;
ROLLBACK TRAN --回滚事务
END CATCH
END
sql事务注意点：
DECLARE @Referenznummer NVARCHAR(MAX)
SET @Referenznummer = '934590-AE1023-1'
BEGIN TRAN
BEGIN TRY
DECLARE @returnValue INT
SET @returnValue = 11110
--(1)、变量在事务中是不会进行回滚的；默认值虽然是11110
SET @returnValue = 1
IF (
NOT EXISTS ( SELECT 1
FROM tbOrder
WHERE Referenznummer = @Referenznummer )
)
BEGIN
--（2）、出现异常后,直接终止下面的代码,跳转到CATCH代码块中.
SELECT 10 / 0
--begin
SELECT @@ERROR
SELECT 100
IF (@@error <> 0) --此语句在try catch中是捕获不到的。
BEGIN
SET @returnValue = 0
END
--end
END
ELSE
BEGIN
SET @returnValue = 3
END
COMMIT TRAN
END TRY
BEGIN CATCH
PRINT 'error'
--SET @returnValue = 0
ROLLBACK TRAN
END CATCH
SELECT @returnValue
--RETURN @returnValue
