在T-SQL中有相当于C＃的“throw”重新抛出异常？ [英] Is there an equivalent in T-SQL to C#'s "throw;" to re-throw exceptions?
本文介绍了在T-SQL中有相当于C＃的“throw”重新抛出异常？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
在C＃中可以这样做：
try
{
DoSomethingThatMightThrowAnException（）;
}
catch（Exception ex）
{
//执行某些异常
throw; //按原样重新抛出。
}
T-SQL的 BEGIN CATCH 执行相同的功能
解决方案
可以使用RAISERROR。从RAISERROR上的 MSDN文档：
BEGIN TRY
- 严重程度为11-19的RAISERROR将导致执行
- 跳转到CATCH块
RAISERROR （TRY块中出现错误， - 消息文本
16， - 严重性
1 - 状态
）;
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR（4000）;
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE（），
@ErrorSeverity = ERROR_SEVERITY（），
@ErrorState = ERROR_STATE（）;
- 在CATCH块内使用RAISERROR返回
- 有关
- 导致执行跳转到CATCH块的原始错误的错误信息。
RAISERROR（@ErrorMessage， - 消息文本
@ErrorSeverity， - 严重性
@ErrorState - State。
）;
END CATCH;
编辑：
这不是真的与c＃的 throw 或 throw ex 相同的东西。正如@henrikstaunpoulsen指出，你没有得到新的错误的原始错误号（RAISERROR被限制在哪个号码可以使用）。您将不得不使用某种约定并解析消息中的信息（如果可用）。
MSDN有一篇文章在Transact-SQL中使用TRY ... CATCH ，我使用了一些代码来创建下面的测试：
使用测试;
GO
IF OBJECT_ID（N'usp_RethrowError'，N'P'）IS NOT NULL
DROP PROCEDURE usp_RethrowError;
GO
CREATE PROCEDURE usp_RethrowError AS
如果ERROR_NUMBER（）IS NULL
返回;
DECLARE
@ErrorMessage NVARCHAR（4000），
@ErrorNumber INT，
@ErrorSeverity INT，
@ErrorState INT，
@ErrorLine INT，
@ErrorProcedure NVARCHAR（200）;
SELECT
@ErrorNumber = ERROR_NUMBER（），
@ErrorSeverity = ERROR_SEVERITY（），
@ErrorState = ERROR_STATE（），
@ErrorLine = ERROR_LINE （），
@ErrorProcedure = ISNULL（ERROR_PROCEDURE（），' - '）;
SELECT @ErrorMessage =
N'Error％d，级别％d，状态％d，过程％s，行％d，'+
'消息：'+ ERROR_MESSAGE （）;
RAISERROR
（
@ErrorMessage，
@ErrorSeverity，
@ErrorState，
@ErrorNumber， - 参数：原始错误编号。
@ErrorSeverity， - 参数：原始错误严重性
@ErrorState， - 参数：原始错误状态
@ErrorProcedure， - 参数：原始错误过程名称
@ErrorLine - 参数：原始错误行号
）;
GO
打印'不收藏'
DROP TABLE XXXX
PRINT'单个收藏'
BEGIN TRY
DROP TABLE XXXX
END TRY
BEGIN CATCH
EXEC usp_RethrowError;
END CATCH;
PRINT'Double Catch'
BEGIN TRY
BEGIN TRY
DROP TABLE XXXX
END TRY
BEGIN CATCH
EXEC usp_RethrowError ;
END CATCH;
END TRY
BEGIN CATCH
EXEC usp_RethrowError;
END CATCH;
其中产生以下输出：
No Catch
Msg 3701，Level 11，State 5，Line 3
无法删除XXXX表，因为它不存在或您没有权限。
单个抓取
消息50000，级别11，状态5，过程usp_RethrowError，行25
错误3701，级别11，状态5，过程 - ，第7行消息：无法删除表' XXXX'，因为它不存在或您没有权限。
Double Catch
消息50000，级别11，状态5，过程usp_RethrowError，行25
错误50000，级别11，状态5，过程usp_RethrowError，行25，消息：错误3701，级别11 ，状态5，过程 - ，行16，消息：不能删除XXXX表，因为它不存在或您没有权限。
The title really is the question for this one: Is there an equivalent in T-SQL to C#'s "throw;" to re-throw exceptions?
In C# one can do this:
try
{
DoSomethingThatMightThrowAnException();
}
catch (Exception ex)
{
// Do something with the exception
throw; // Re-throw it as-is.
}
Is there something in T-SQL's BEGIN CATCH functionality that does the same?
解决方案
You can use RAISERROR. From the MSDN documentation on RAISERROR:
BEGIN TRY
-- RAISERROR with severity 11-19 will cause execution to
-- jump to the CATCH block
RAISERROR ('Error raised in TRY block.', -- Message text.
16, -- Severity.
1 -- State.
);
END TRY
BEGIN CATCH
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.
);
END CATCH;
EDIT:
This is not really the same thing as c#'s throw or throw ex. As @henrikstaunpoulsen points out you don't get the original error number in the new error (RAISERROR is restricted in which numbers it can use). You would have to use some sort of convention and parse the information (if available) out of the message.
MSDN has an article Using TRY...CATCH in Transact-SQL and I used some of the code to create the test below:
use test;
GO
IF OBJECT_ID (N'usp_RethrowError',N'P') IS NOT NULL
DROP PROCEDURE usp_RethrowError;
GO
CREATE PROCEDURE usp_RethrowError AS
IF ERROR_NUMBER() IS NULL
RETURN;
DECLARE
@ErrorMessage NVARCHAR(4000),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorLine INT,
@ErrorProcedure NVARCHAR(200);
SELECT
@ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
SELECT @ErrorMessage =
N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +
'Message: '+ ERROR_MESSAGE();
RAISERROR
(
@ErrorMessage,
@ErrorSeverity,
@ErrorState,
@ErrorNumber, -- parameter: original error number.
@ErrorSeverity, -- parameter: original error severity.
@ErrorState, -- parameter: original error state.
@ErrorProcedure, -- parameter: original error procedure name.
@ErrorLine -- parameter: original error line number.
);
GO
PRINT 'No Catch'
DROP TABLE XXXX
PRINT 'Single Catch'
BEGIN TRY
DROP TABLE XXXX
END TRY
BEGIN CATCH
EXEC usp_RethrowError;
END CATCH;
PRINT 'Double Catch'
BEGIN TRY
BEGIN TRY
DROP TABLE XXXX
END TRY
BEGIN CATCH
EXEC usp_RethrowError;
END CATCH;
END TRY
BEGIN CATCH
EXEC usp_RethrowError;
END CATCH;
Which produces the following output:
No Catch
Msg 3701, Level 11, State 5, Line 3
Cannot drop the table 'XXXX', because it does not exist or you do not have permission.
Single Catch
Msg 50000, Level 11, State 5, Procedure usp_RethrowError, Line 25
Error 3701, Level 11, State 5, Procedure -, Line 7, Message: Cannot drop the table 'XXXX', because it does not exist or you do not have permission.
Double Catch
Msg 50000, Level 11, State 5, Procedure usp_RethrowError, Line 25
Error 50000, Level 11, State 5, Procedure usp_RethrowError, Line 25, Message: Error 3701, Level 11, State 5, Procedure -, Line 16, Message: Cannot drop the table 'XXXX', because it does not exist or you do not have permission.
这篇关于在T-SQL中有相当于C＃的“throw”重新抛出异常？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
