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 - 1
BEGIN TRY
SELECT 1/0 as DivideByZero
END TRY
BEGIN CATCH
THROW;
END CATCH
GO
结果
(0 row(s) affected)
Msg 8134, Level 16, State 1, Line 6
Divide 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 DivideByZero
END TRY
BEGIN 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 NOWAIT
END CATCH
GO
结果
(0 row(s) affected)
Msg 50000, Level 16, State 1, Line 19
Error occurred while retrieving the data from database: Divide by zero error encountered.
参考: