SQL Server ->> THROW字句對比RAISERROR字句

標籤：

SQL Server 2012開始引入了THROW字句用於替代從SQL Server開始沿用至今的RAISERROR。既然作用相同，都是在TRY... CATCH代碼塊後不抓錯誤然後拋出錯誤，它們之間的差異是什嗎？

RAISERROR statement	THROW statement
If a msg_id is passed to RAISERROR, the ID must be defined in sys.messages.	The error_number parameter does not have to be defined in sys.messages.
The msg_str parameter can contain printf formatting styles.	The message parameter does not accept printf style formatting.
The severity parameter specifies the severity of the exception.	There is no severity parameter. The exception severity is always set to 16.

 

上表列出了它們的差異

THROW對比RAISERROR最大的優勢在於，我們不再需要在CATCH代碼塊裡面捕捉到ERROR_MESSAGE()， ERROR_STATE()等幾個系統函數的值然後賦值給變數，只需要一個THROW語句就可以完成原本RAISERROR需要賦值變數再通過參數傳入拋出異常的過程。而且對於錯誤行的捕捉也是直接指向錯誤行，而不是像RAISERROR那樣是RAISERROR語句發生時的程式碼。而且THROW還是可以像RAISERROR一樣可以傳參，效果和RAISERROR是一樣的。

 

總結就是RAISERROR可以做的THROW都可以做到，而THROW還可以節省RAISERROR原本需要多個步驟完成的事情。而微軟也是推薦用THROW去代替RAISERROR。

 

-- Using THROW - 1BEGIN TRY SELECT 1/0 as DivideByZeroEND TRYBEGIN CATCH THROW;END CATCHGO

結果

(0 row(s) affected)Msg 8134, Level 16, State 1, Line 6Divide by zero error encountered.

 

而如果用RAISERROR

USE [JerryDB]GO-- Using RAISERROR()DECLARE  @ERR_MSG AS NVARCHAR(4000) ,@ERR_SEV AS SMALLINT ,@ERR_STA AS SMALLINT BEGIN TRY SELECT 1/0 as DivideByZeroEND TRYBEGIN CATCH SELECT @ERR_MSG = ERROR_MESSAGE(), @ERR_SEV =ERROR_SEVERITY(), @ERR_STA = ERROR_STATE() SET @ERR_MSG= ‘Error occurred while retrieving the data from database: ‘ + @ERR_MSG  RAISERROR (@ERR_MSG, @ERR_SEV, @ERR_STA) WITH NOWAITEND CATCHGO

結果

(0 row(s) affected)Msg 50000, Level 16, State 1, Line 19Error occurred while retrieving the data from database: Divide by zero error encountered.

 

 

 

參考：

New THROW statement in SQL Server 2012 (vs RAISERROR)

SQL Server ->> THROW字句對比RAISERROR字句