sqlserver 預存程序 try catch TRANSACTION (轉)

標籤：

原文：http://www.cnblogs.com/yun_shuai/archive/2010/09/20/1831546.html

 

/*

1. 輕微錯誤（嚴重性層級為0-10）：預設情況下不會給客戶程式發送錯誤訊息，繼續工作。也就是說它無法被CATCH到

2. 中等錯誤（嚴重性層級為11-19）：能夠被CATCH到（不管是在T-SQL裡面還是在客戶程式裡面）

3. 嚴重錯誤（嚴重性層級為20-25）：SQL Server將強制把串連關掉。很顯然這也不可能被CATCH到


【重點提示！！】

由於業務的複雜或者系統效能問題，致使資料庫sql語句執行較久。

導致用戶端網頁已經連線逾時（如設定為30秒）

此時資料庫批處理語句未執行完成，客戶session斷開，相當於中斷操作，類似【案例1】的中斷。

事務是沒有提交或復原的，資源仍在佔用，導致發生堵塞或死結！~



【解決方案】：

在批處理語句前加上 SET XACT_ABORT ON
當用戶端中斷的時候，未執行完成則復原操作，及時釋放資源。

--查看 XACT_ABORT 是否開啟
SELECT (CASE WHEN (16384 & @@OPTIONS) = 16384 THEN ‘ON‘ ELSE ‘OFF‘ END) AS XACT_ABORT;

*/

 

 

 

CREATE PROCEDURE YourProcedure    
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY---------------------開始捕捉異常
       BEIN TRAN------------------開始事務
        UPDATE A SET A.names = B.names FROM 表1 AS A INNER JOIN 表2 AS B ON A.id = B.id

        UPDATE A SET A.names = B.names FROM 表1 AS A INNER JOIN 表2 AS B ON A.TEST = B.TEST

    COMMIT TRAN -------提交事務
    END TRY-----------結束捕捉異常
    BEGIN CATCH------------有異常被捕獲
        IF @@TRANCOUNT > 0---------------判斷有沒有事務
        BEGIN
            ROLLBACK TRAN----------復原事務
        END 
        EXEC YourLogErrorProcedure-----------執行預存程序將錯誤資訊記錄在表當中
    END CATCH--------結束異常處理
END

 

---------------------------------------------記錄操作錯資訊的預存程序--------------------------------------------

CREATE PROCEDURE YourLogErrorProcedure
    @ErrorLogID [int] = 0 OUTPUT -- contains the ErrorLogID of the row inserted
AS                               -- by uspLogError in the ErrorLog table
BEGIN
    SET NOCOUNT ON;

    -- Output parameter value of 0 indicates that error 
    -- information was not logged
    SET @ErrorLogID = 0;

    BEGIN TRY
        -- Return if there is no error information to log
        IF ERROR_NUMBER() IS NULL
            RETURN;

        -- Return if inside an uncommittable transaction.
        -- Data insertion/modification is not allowed when 
        -- a transaction is in an uncommittable state.
        IF XACT_STATE() = -1
        BEGIN
            PRINT ‘Cannot log error since the current transaction is in an uncommittable state. ‘ 
                + ‘Rollback the transaction before executing uspLogError in order to successfully log error information.‘;
            RETURN;
        END

        INSERT [dbo].[OperateErrorLog] 
            (
            [OperateName], 
            [ErrorNumber], 
            [ErrorSeverity], 
            [ErrorState], 
            [ErrorProcedure], 
            [ErrorLine], 
            [ErrorMessage]
            ) 
        VALUES 
            (
            CONVERT(sysname, CURRENT_USER), 
            ERROR_NUMBER(),
            ERROR_SEVERITY(),
            ERROR_STATE(),
            ERROR_PROCEDURE(),
            ERROR_LINE(),
            ERROR_MESSAGE()
            );
        SET @ErrorLogID = @@IDENTITY;
    END TRY
    BEGIN CATCH
        PRINT ‘An error occurred in stored procedure uspLogError: ‘;
        EXECUTE YourPrintErrorProcedure;-----------------列印錯誤資訊的預存程序
        RETURN -1;
    END CATCH
END;

 

CREATE PROCEDURE YourPrintErrorProcedure
AS
BEGIN
    SET NOCOUNT ON;

    -- Print error information. 
    PRINT ‘Error ‘ + CONVERT(varchar(50), ERROR_NUMBER()) +
          ‘, Severity ‘ + CONVERT(varchar(5), ERROR_SEVERITY()) +
          ‘, State ‘ + CONVERT(varchar(5), ERROR_STATE()) + 
          ‘, Procedure ‘ + ISNULL(ERROR_PROCEDURE(), ‘-‘) + 
          ‘, Line ‘ + CONVERT(varchar(5), ERROR_LINE());
    PRINT ERROR_MESSAGE();
END;

CREATE TABLE [dbo].[ErrorLog](
    [ErrorLogID] [int] IDENTITY(1,1) NOT NULL,
    [ErrorTime] [datetime] NOT NULL CONSTRAINT [DF_ErrorLog_ErrorTime]  DEFAULT (getdate()),
    [UserName] [sysname] COLLATE Chinese_PRC_CI_AS NOT NULL,
    [ErrorNumber] [int] NOT NULL,
    [ErrorSeverity] [int] NULL,
    [ErrorState] [int] NULL,
    [ErrorProcedure] [nvarchar](126) COLLATE Chinese_PRC_CI_AS NULL,
    [ErrorLine] [int] NULL,
    [ErrorMessage] [nvarchar](4000) COLLATE Chinese_PRC_CI_AS NOT NULL,
 CONSTRAINT [PK_ErrorLog_ErrorLogID] PRIMARY KEY CLUSTERED 
(
    [ErrorLogID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

 

本文來自CSDN部落格，轉載請標明出處：http://blog.csdn.net/WeiZhang_son_Ding/archive/2010/02/05/5291732.aspx

 

 

http://www.cnblogs.com/BpLoveGcy/archive/2010/03/22/1691407.html

ALTER PROC usp_AccountTransaction  

1.     @AccountNum INT,  
2.     @Amount DECIMAL  
3. AS  
4. BEGIN  
5. BEGIN TRY --Start the Try Block..  
6. BEGIN TRANSACTION -- Start the transaction..  
7. UPDATE MyChecking SET Amount = Amount - @Amount  
8. WHERE AccountNum = @AccountNum  
9. UPDATE MySavings SET Amount = Amount + @Amount  
10. WHERE AccountNum = @AccountNum  
11. COMMIT TRAN -- Transaction Success!  
12. END TRY  
13. BEGIN CATCH  
14.         IF @@TRANCOUNT > 0  
15. ROLLBACK TRAN --RollBack in case of Error  
16. -- you can Raise ERROR with RAISEERROR() Statement including the details of the exception  
17.         --RAISERROR(ERROR_MESSAGE(), ERROR_SEVERITY(), 1)       

            DECLARE @ErrorMessage NVARCHAR(4000);
   　　　　DECLARE @ErrorSeverity INT;
  　　 DECLARE @ErrorState INT;

   　　SELECT 
    　　@ErrorMessage = ERROR_MESSAGE(),
    　　@ErrorSeverity = ERROR_SEVERITY(),
    　　@ErrorState = ERROR_STATE();

   -- Use RAISERROR inside the CATCH block to return error
   -- information about the original error that caused
   -- execution to jump to the CATCH block.
   　　RAISERROR (@ErrorMessage, -- Message text.
        　　@ErrorSeverity, -- Severity.
        　　@ErrorState -- State.
       　　 );

1.   
2.     END CATCH  
3.   
4. END  
5.   
6. GO  

 

1. BEGIN TRY  
2.   
3.     SELECT GETDATE()  
4.   
5.     SELECT 1/0--Evergreen divide by zero example!  
6.   
7. END TRY  
8.   
9. BEGIN CATCH  
10.   
11.     SELECT ‘There was an error! ‘ + ERROR_MESSAGE()  
12.   
13.     RETURN  
14.   
15. END CATCH;  

 

2.獲得錯誤資訊的函數表: 

 

下面系統函數在CATCH塊有效.可以用來得到更多的錯誤資訊:

函數	描述
ERROR_NUMBER()	返回導致運行 CATCH 塊的錯誤訊息的錯誤號碼。
ERROR_SEVERITY()	返回導致 CATCH 塊啟動並執行錯誤訊息的嚴重層級
ERROR_STATE()	返回導致 CATCH 塊啟動並執行錯誤訊息的狀態號
ERROR_PROCEDURE()	返回出現錯誤的預存程序名稱
ERROR_LINE()	返回傳生錯誤的行號
ERROR_MESSAGE()	返回導致 CATCH 塊啟動並執行錯誤訊息的完整文本

 

•   BEGIN TRY  
•   
•     Try Statement 1  
•   
•     Try Statement 2  
•   
•     ...  
•   
•     Try Statement M  
•   
• END TRY  
•   
• BEGIN CATCH  
•   
•     Catch Statement 1  
•   
•     Catch Statement 2  
•   
•     ...  
•   
•     Catch Statement N  
•   
• END CATCH  

sqlserver 預存程序 try catch TRANSACTION (轉)