SQL中存储过程中使用事务,并且加入异常处理机制.

--存储过程中使用事务,并且加入异常处理机制.
-- =============================================
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
--存储过程中使用事务,并且加入异常处理机制.
-- ================