从 SQL Server 函数向存储过程抛出异常
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15836759/
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
Throw exception from SQL Server function to stored procedure
提问by MaxRecursion
I have stored procedure in SQL Server 2012 say spXample and a scaler-valued function say fXample.
I call a function fXample from spXample.
Can I throw an exception in function and catch it in stored procedure's Catchblock and rethrow to the calling C# code?
我在 SQL Server 2012 中有存储过程说 spXample 和一个缩放器值函数说 fXample。我从 spXample 调用函数 fXample。我可以在函数中抛出异常并在存储过程的Catch块中捕获它并重新抛出到调用 C# 代码吗？
Update:
更新：
The function I wrote like:
我写的函数如下：
CREATE FUNCTION dbo.fXample(@i INT)
RETURNS TINYINT
AS
BEGIN
RETURN (SELECT CASE WHEN @i < 10
THEN THROW 51000,'Xample Exception',1;
ELSE (SELECT @i)
END);
END
GO
I am getting error
我收到错误
Msg 443, Level 16, State 14, Procedure fXample, Line 46 Invalid use of a side-effecting operator 'THROW' within a function.
消息 443，级别 16，状态 14，过程 fXample，第 46 行在函数内无效使用副作用运算符“THROW”。
How do I write alternative code to achieve above functionality?
如何编写替代代码来实现上述功能？
回答by Aaron Bertrand
You can do this by forcing an error condition when your validation fails, provided that isn't a possible error that might occur naturally. When you know a certain error can only occur when validation has failed, you can handle that in a custom way by checking for that error_number in your catch block. Example in tempdb:
您可以通过在验证失败时强制出现错误条件来做到这一点，前提是这不是可能自然发生的错误。当您知道某个错误只能在验证失败时发生时，您可以通过检查 catch 块中的 error_number 以自定义方式处理该错误。tempdb 中的示例：
USE tempdb;
GO
CREATE FUNCTION dbo.fXample(@i INT)
RETURNS TINYINT
AS
BEGIN
RETURN (SELECT CASE WHEN @i < 10 -- change this to your "validation failed" condition
THEN 1/0 -- something that will generate an error
ELSE (SELECT @i) -- (you'd have your actual retrieval code here)
END);
END
GO
CREATE PROCEDURE dbo.spXample
@i INT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
SELECT dbo.fXample(@i);
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 8134 -- divide by zero
BEGIN
THROW 50001, 'Your custom error message.', 1;
-- you can throw any number > 50000 here
END
ELSE -- something else went wrong
BEGIN
THROW; -- throw original error
END
END CATCH
END
GO
Now try it out:
现在试试看：
EXEC dbo.spXample @i = 10; -- works fine
EXEC dbo.spXample @i = 6; -- fails validation
EXEC dbo.spXample @i = 256; -- passes validation but overflows return
Results:
结果：
----
10
Msg 50001, Level 16, State 1, Procedure spXample, Line 12
Your custom error message.Msg 220, Level 16, State 2, Procedure spXample, Line 7
Arithmetic overflow error for data type tinyint, value = 256.
消息 50001，级别 16，状态 1，过程 spXample，第 12 行
您的自定义错误消息。消息 220，级别 16，状态 2，过程 spXample，第 7 行
数据类型 tinyint 的算术溢出错误，值 = 256。
