在 T-SQL 中手动引发错误以跳转到 BEGIN CATCH 块
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1531450/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Raise an error manually in T-SQL to jump to BEGIN CATCH block
提问by abatishchev
Is it possible to raise an error in a stored procedure manually to stop execution and jump to BEGIN CATCHblock? Some analog of throw new Exception()in C#.
是否可以手动在存储过程中引发错误以停止执行并跳转到BEGIN CATCH块？throw new Exception()in 的一些模拟C#。
Here is my stored procedure's body:
这是我的存储过程的主体：
BEGIN TRY
BEGIN TRAN
-- do something
IF @foobar IS NULL
-- here i want to raise an error to rollback transaction
-- do something next
COMMIT TRAN
END TRY
BEGIN CATCH
IF @@trancount > 0
ROLLBACK TRAN
END CATCH
I know one way: SELECT 1/0But it's awful!!
我知道一种方式：SELECT 1/0但这太糟糕了！！
回答by TheVillageIdiot
you can use raiserror. Read more details here
你可以使用raiserror. 在此处阅读更多详细信息
--from MSDN
——来自 MSDN
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;
EDITIf you are using SQL Server 2012+ you can use throwclause. Hereare the details.
编辑如果您使用的是 SQL Server 2012+，则可以使用throw子句。这是详细信息。
回答by Jim Aho
You could use THROW(available in SQL Server 2012+):
您可以使用THROW（在 SQL Server 2012+ 中可用）：
THROW 50000, 'Your custom error message', 1
THROW <error_number>, <message>, <state>
回答by Donut
You're looking for RAISERROR.
您正在寻找RAISERROR.
From MSDN:
来自 MSDN：
Generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view or build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY…CATCH construct.
生成错误消息并启动会话的错误处理。RAISERROR 可以引用存储在 sys.messages 目录视图中的用户定义消息或动态构建消息。该消息作为服务器错误消息返回到调用应用程序或 TRY…CATCH 构造的关联 CATCH 块。
CodeProject has a good articlethat also describes in-depth the details of how it works and how to use it.
CodeProject 有一篇很好的文章，还深入介绍了它是如何工作的以及如何使用它的细节。
回答by Andrew
SQL has an error raising mechanism
SQL 有一个错误引发机制
RAISERROR ( { msg_id | msg_str | @local_variable }
{ ,severity ,state }
[ ,argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]
Just look up Raiserror in the Books Online. But.. you have to generate an error of the appropriate severity, an error at severity 0 thru 10 do not cause you to jump to the catch block.
只需在联机丛书中查找 Raiserror。但是..你必须生成一个适当严重性的错误，严重性 0 到 10 的错误不会导致你跳转到 catch 块。
回答by sof_user
THROW (Transact-SQL)
扔 (Transact-SQL)
Raises an exception and transfers execution to a CATCH block of a TRY…CATCH construct in SQL Server 2017.
引发异常并将执行转移到 SQL Server 2017 中 TRY…CATCH 构造的 CATCH 块。
Please refer the below link
请参考以下链接
