在SQL Server2005中進行錯誤捕捉。

  任何程式都可能出現錯誤，在 SQL Server 中執行 Transact-SQL 也不例外。如果在 Transact-SQL 中發生了錯誤，一般有兩種捕捉錯誤的方法，一種是在用戶端代碼（如 C# 、 Delphi 等 ) 中使用類似 try...catch 的語句進行捕捉；另外一種就是在 Transact-SQL 中利用 Transact-SQL 本身提供的錯誤捕捉機制進行捕捉。如果是因為 Transact-SQL 語句的執行而産生的錯誤，如鍵值沖突，使用第一種和第二種方法都可以捕捉，但是如果是邏輯錯誤，使用用戶端代碼進行捕捉就不太友善。是以，本文就如何使用 Transact-SQL 進行錯誤捕捉進行了讨論。 一、非緻命錯誤 (non-fatal error) 的捕捉 通過執行 Transact-SQL 而産生的錯誤可分為兩種：緻命錯誤 (fatal error) 和非緻命錯誤 (non-fatal error) 。在 Transact-SQL 中隻可以捕捉非緻命錯誤 ( 如鍵值沖突 ) ，而無法捕捉緻命錯誤 ( 如文法錯誤 ) 。在 Transact-SQL 中可以通過系統變量 @@ERROR 判斷最近執行的一條語句是否成功執行。如果發生了錯誤， @@Error 的值大于 0 ，否則值為 0 。下面舉一個例子說明 @@ERROR 的使用。 假設有一個表 table1 ，在這個表中有兩個字段 f1 ， f2 。其中 f1 是主鍵。 INSERT INTO table1 VALUES(1, 'aa') INSERT INTO table1 VALUES(1, 'bb')  -- 這條語句将産生一個錯誤 IF @@ERROR > 0 PRINT ' 鍵值沖突 ' 當執行第二條語句時發生鍵值沖突錯誤， @@ERROR 被賦為錯誤号 2627 ，是以輸出結果顯示 ' 鍵值沖突 ' 。使用 @@ERROR 系統變量時需要注意， @@ERROR 隻記錄最近一次執行的 Transact-SQL 語句所發生的錯誤，如果最近一次執行的 Transact-SQL 沒有發生錯誤， @@ERROR 的值為 0 。是以，隻能在被捕捉的那條 Transact-SQL 語句後使用 @@ERROR 。 在 SQL Server 中，不僅可以捕捉系統提供的錯誤，還可以自定義錯誤。有兩種方法可以定義錯誤資訊。 1 、使用 sp_addmessage 系統存儲過程添加錯誤資訊，然後使用 RAISERROR 抛出錯誤。 sp_addmessage 将錯誤号，錯誤級别、錯誤描述等資訊添加到系統表中，然後使用 RAISERROR 根據相應的錯誤号抛出錯誤資訊。使用者自定義的資訊應該從 50001 開始。 EXEC sp_addmessage @msgnum = 50001, @severity = 16, @msgtext = 'sql encounter an error(%s).', @lang = 'us_english' EXEC sp_addmessage @msgnum = 50001, @severity = 16, @msgtext = 'sql 遇到了一個錯誤 (%1!).' 如果使用的 SQL Server 版本是非英語版本，在添加本地錯誤資訊時必須首先添加英文的錯誤資訊。錯誤描述可以象 c 語言中的 printf 的格式字元串一樣使用參數，如 %s 、 %d 。但要注意的是在英文版的錯誤資訊中要使用 %s 、 %d 等形式，而在本地化的錯誤資訊中要使用 %1! 、 %2! 等形式，在每個 %?(1 <= ? <= n) 後需要加一個 ! ，而且 %? 的數目必須和英文版的錯誤資訊的參數一緻。 在未插入本地化錯誤資訊時， RAISERROR 将使用英文版的錯誤資訊。當插入本地化錯誤資訊時， RAISERROR 使用本地化的錯誤資訊。 RAISERROR(50001, 16, 1, ' 測試 ') 輸出的結果： 伺服器 : 消息 50001 ，級别 16 ，狀态 1 ，行 1 sql 遇到了一個錯誤 ( 測試 ). 其中 ' 測試 ' 字元串通過 %1 傳入本地化的錯誤描述字元串中。 2 、直接使用 RAISERROR 将錯誤抛出。 使用第一種方法雖然使 Transact-SQL 語句看上去更整潔（這種方法類似于在程式設計語言中使用常量定義錯誤資訊，然後在不同的地方通過錯誤編号引用這些錯誤資訊。 ) ，但是這樣做卻使錯誤資訊和資料庫的耦合度增加，因為如果将這些帶有 RAISERROR 的 Transact-SQL 放到别的 SQL Server 資料庫上執行，由于在其它的資料庫中還未添加錯誤資訊，是以會産生 RAISERROR 調用錯誤，除非使用 sp_addmessage 将所需的錯誤資訊再加入到其它的資料庫中。 基于上述原因， RAISERROR 不僅可以根據錯誤代碼抛出錯誤資訊，也可以直接通過錯誤描述格式字元串抛出錯誤資訊。 RAISERROR('sql 遇到了一個錯誤 (%s)', 16, 1, ' 測試 ') 二、邏輯錯誤的捕捉 在實際應用中，更多的是由于某些業務要求而産生的邏輯錯誤。這些錯誤無法通過 @@ERROR 進行捕捉。如果使用用戶端代碼進行捕捉，那麼 Transact-SQL 必須一條一條地執行。如果使用存儲過程，那麼發生在存儲過程内部的邏輯錯誤就很難在用戶端代碼中進行捕捉，是以，下面将讨論如何使用 Transact-SQL 捕捉邏輯錯誤。 所謂邏輯錯誤，就是在執行完 Transact-SQL 後，執行結果與業務要求的結果不符而産生的。為了說明如何處理邏輯錯誤，我們再建立一個表 table2 ，這個表的結構和 table1 完全一樣，隻是 f1 字段不再是主鍵了。然後建立一個存儲過程，它的功能是在 table1 和 table2 中同時插入一條記錄，但是這條記錄必須滿足兩個條件。 1 、 f1 值不能大于 100 。 2 、要插入的記錄在 table1 中不存在，如果存在，在 table1 和 table2 中都不插入這條記錄。

CREATE PROCEDURE p1(@Num int) AS DECLARE @Error int, @RowCount int BEGIN TRANSACTION INSERT INTO table2 VALUES(@Num, 'p') IF @Num > 100 BEGIN RAISERROR('%s 的值不能大于100。', 16, 1, '@Num')   ROLLBACK TRANSACTION RETURN 1 END ELSE BEGIN SELECT f1 FROM table1 WHERE f1 = @Num IF @@ROWCOUNT > 0 BEGIN RAISERROR('table1中已經存在%d了。', 16, 1, @Num) ROLLBACK TRANSACTION RETURN 2 END ELSE BEGIN INSERT INTO table1 VALUES(@Num, 'p') COMMIT TRANSACTION RETURN 0 END END

在這個存儲過程中一開始使用 BEGIN TRANSACTION 顯示地開始一個事務，然後當上述兩種錯誤發生時使用 ROLLBACK TRANSACTION 恢複到初始狀态，如果成功插入，使用 COMMIT TRANSACTION 送出改變。可以通過如下語句進行調用。 DECLARE @ErrNum int EXEC @ErrNUm = p1 2 PRINT @ErrNum 可以通過 @ErrNum 得到 p1 傳回的錯誤代碼，如果傳回 0 ，表示執行成功。 sql Server2005 中錯誤捕捉的新功能 雖然在以前的 SQL Server 版本中可以通過一些技巧實作錯誤捕捉，但有時需要增加一些額外的開銷，如在 p1 中使用了 SELECT 語句。慶幸的是在 SQL Server2005 中提供了和大多數程式設計語言類似的 try...catch 錯誤捕捉功能，進而使 Transact-SQL 第一次可以真正地進行錯誤捕捉。使用 try...catch 可以将 p1 的下半部分改寫為如下形式。

ELSE BEGIN BEGIN TRY INSERT INTO table1 VALUES(@Num, 'p')   COMMIT TRANSACTION RETURN 0 END TRY BEGIN CATCH RAISERROR('table1 中已經存在%d了。', 16, 1, @Num) ROLLBACK TRANSACTION RETURN 2 END CATCH END

可以看出，這個改寫的部分未使用 SELECT 查詢 table1 中是否已經有了某條記錄，而是通過資料庫的限制來進行判斷的。如果鍵值沖突，就産生了錯誤，這樣 SQL 語句就直接跳到 BEGIN CATCH 中執行錯誤處理代碼。這樣做效率要比上一個版本高得多，而且如果将 RAISERROR 去掉， p1 就不會抛出任何錯誤，隻是傳回了一個錯誤碼，這樣有利于用戶端代碼進行處理。

在Transact-SQL中進行錯誤捕捉，如果使用的是SQL Server2005，我的建議是盡量使用try...catch，是以它會捕捉到未預料到的錯誤，并且會使Transact-SQL更容易維護。當然，這樣做就無法将Transact-SQL移植到SQL Server2000或更低的版本上運作，要是想寫通用的Transact-SQL，還是使用傳統的方法捕捉錯誤吧！ <script type=text/javascript> google_ad_client = "pub-6430022987645146"; google_ad_slot = "5930513112"; google_ad_width = 728; google_ad_height = 15; </script>

 v