[SQL]舊的RAISERROR語法在SQL 2012不相容問題

2013-08-08

7897
0
SQL

raiserror error_number error_msg 在SQL 2012居然不能用了!
那要如何滿足SQL 2005 ~ SQL 2012 透過 RAISERROR 來判斷@@ERROR不為0的做法呢?

最近有一支Store Procedure，裡面有使用RAISERROR。而操作的方式是執行它後，並讀取@@ERROR的值來判斷有沒有錯誤，如下，


CREATE PROC RaiseErrorTest
AS 
    BEGIN
        RAISERROR 13001 'this is 13001 error';
    END
GO

DECLARE @errNum INT;
SET @errNum = 0;

EXEC RaiseErrorTest;
SET @errNum = @@ERROR;
--如果沒有錯誤，就commit交易
IF @errNum = 0 
BEGIN
	PRINT '執行OK Commit'
END
ELSE
BEGIN
	PRINT '執行失敗 Rollback'
END

執行結果會顯示「執行失敗 Rollback」，如下，

Msg 13001, Level 16, State 1, Procedure RaiseErrorTest, Line 4
this is 13001 error
執行失敗 Rollback

但是在SQL 2012中一執行到該Store Procedure，就發生以下的錯誤，

Msg 102, Level 15, State 1, Procedure RaiseErrorTest, Line 4
接近 '13001' 之處的語法不正確。

原因是因為我們使用了以下的RAISERROR語法過期了(從Sybase來的，可參考:raiserror)

而在SQL 2008R2就說未來版本不Support以上的寫法(SQL 2012還真的給我不Support 嚎啕大哭 )! 請參考:Deprecated Database Engine Features in SQL Server 2008 R2

不過，那為何那支Store Procedure，可以被建立到SQL 2012的DB之中呢?

因為那個DB是後SQL 2005備份好之後，再Restore到SQL 2012上，所以如果要重新將該Store Procedure執行到該DB的話，就會發生同樣的問題。

知道了問題所在就要加以調整它，如下，


CREATE PROC RaiseErrorTest2
AS 
    BEGIN
        RAISERROR('this is 13001 error', 10, 1); 
    END
GO

再執行以下的SQL，


DECLARE @errNum INT;
SET @errNum = 0;

EXEC RaiseErrorTest2;
SET @errNum = @@ERROR;
IF @errNum = 0 
BEGIN
	PRINT '執行OK Commit'
END
ELSE
BEGIN
	PRINT '執行失敗 Rollback'
END

但執行結果卻顯示「執行OK Commit」，天呀! 我的@@ERROR的值變成0了!如下，

this is 13001 error
執行OK Commit

表示如果使用RAISERROR('this is 13001 error', 10, 1)的方式，@@ERROR的值卻是0。

於是想要用THROW，但這樣呼叫的程式也要一併修改，就要調查程式中有多少使用到這種方式，然後加以調整，但是THROW又不能用在SQL 2012之前的版本。

…

於是筆者想到一個很瞎的作法，但卻能暫時滿足SQL 2005 ~ SQL 2012的這種透過 RAISERROR 來判斷@@ERROR不為0的做法。

就是使用RAISERROR(msg_id , 10, 1)，因為使用msg_id的話，需要透過 sp_addmessage 將訊息加入SQL之中，如果沒有的話，就會產生錯誤。

所以利用這錯誤，@@ERROR就會變成不為0，呼叫的程式就可以判斷了，如下，


CREATE PROC RaiseErrorTest3
AS 
    BEGIN
        RAISERROR(50001, 10, 1); 
    END
GO

再執行以下的SQL，


DECLARE @errNum INT;
SET @errNum = 0;

EXEC RaiseErrorTest3;
SET @errNum = @@ERROR;
IF @errNum = 0 
BEGIN
	PRINT '執行OK Commit'
END
ELSE
BEGIN
	PRINT '執行失敗 Rollback'
END

執行結果會顯示「執行失敗 Rollback」(只是@@ERROR的值變成了18054，而不是我們要的50001，但針對我們舊有「只判斷@@ERROR不為0」的做法算是可以相容過去)，如下，

Msg 18054, Level 16, State 1, Procedure RaiseErrorTest3, Line 4
Error 50001, severity 10, state 1 was raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage.
執行失敗 Rollback