SQL 进行存储过程参数验证的“正确”方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1057959/
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
The "right" way to do stored procedure parameter validation
提问by VVS
I have a stored procedure that does some parameter validation and should fail and stop execution if the parameter is not valid.
我有一个执行一些参数验证的存储过程，如果参数无效，它应该失败并停止执行。
My first approach for error checking looked like this:
我的第一种错误检查方法如下所示：
create proc spBaz
(
@fooInt int = 0,
@fooString varchar(10) = null,
@barInt int = 0,
@barString varchar(10) = null
)
as
begin
if (@fooInt = 0 and (@fooString is null or @fooString = ''))
raiserror('invalid parameter: foo', 18, 0)
if (@barInt = 0 and (@barString is null or @barString = ''))
raiserror('invalid parameter: bar', 18, 0)
print 'validation succeeded'
-- do some work
end
This didn't do the trick since severity 18 doesn't stop the execution and 'validation succeeded' is printed together with the error messages.
这并没有奏效，因为严重性 18 不会停止执行，并且“验证成功”与错误消息一起打印。
I know I could simply add a return after every raiserror but this looks kind of ugly to me:
我知道我可以在每次 raiserror 后简单地添加一个 return ，但这对我来说看起来有点难看：
if (@fooInt = 0 and (@fooString is null or @fooString = ''))
begin
raiserror('invalid parameter: foo', 18, 0)
return
end
...
print 'validation succeeded'
-- do some work
Since errors with severity 11 and higher are caught within a try/catch block another approach I tested was to encapsulate my error checking inside such a try/catch block. The problem was that the error was swallowed and not sent to the client at all. So I did some research and found a way to rethrowthe error:
由于在 try/catch 块中捕获了严重性为 11 及更高的错误，因此我测试的另一种方法是将我的错误检查封装在这样的 try/catch 块中。问题是错误被吞了，根本没有发送给客户端。所以我做了一些研究，找到了重新抛出错误的方法：
begin try
if (@fooInt = 0 and (@fooString is null or @fooString = ''))
raiserror('invalid parameter: foo', 18, 0)
...
end try
begin catch
exec usp_RethrowError
return
end catch
print 'validation succeeded'
-- do some work
I'm still not happy with this approach so I'm asking you:
我仍然对这种方法不满意，所以我问你：
How does your parameter validation look like? Is there some kind of "best practice" to do this kind of checking?
您的参数验证如何？是否有某种“最佳实践”来进行这种检查？
回答by LukeH
I don't think that there is a single "right" way to do this.
我认为没有一种“正确”的方法可以做到这一点。
My own preference would be similar to your second example, but with a separate validation step for each parameter and more explicit error messages.
我自己的偏好与您的第二个示例类似，但每个参数都有一个单独的验证步骤和更明确的错误消息。
As you say, it's a bit cumbersome and ugly, but the intent of the code is obvious to anyone reading it, and it gets the job done.
正如你所说，它有点麻烦和丑陋，但代码的意图对任何阅读它的人来说都是显而易见的，它完成了工作。
IF (ISNULL(@fooInt, 0) = 0)
BEGIN
RAISERROR('Invalid parameter: @fooInt cannot be NULL or zero', 18, 0)
RETURN
END
IF (ISNULL(@fooString, '') = '')
BEGIN
RAISERROR('Invalid parameter: @fooString cannot be NULL or empty', 18, 0)
RETURN
END
回答by Andomar
We normally avoid raiseerror() and return a value that indicates an error, for example a negative number:
我们通常避免 raiseerror() 并返回一个指示错误的值，例如一个负数：
if <errorcondition>
return -1
Or pass the result in two out parameters:
或者将结果传入两个输出参数：
create procedure dbo.TestProc
....
@result int output,
@errormessage varchar(256) output
as
set @result = -99
set @errormessage = null
....
if <errorcondition>
begin
set @result = -1
set @errormessage = 'Condition failed'
return @result
end
回答by Andras Zoltan
As you can see from this answer history I followed this question and accepted answer, and then proceeded to 'invent' a solution that was basically the same as your second approach.
正如您从这个答案历史中看到的那样，我遵循了这个问题并接受了答案，然后继续“发明”了一个与您的第二种方法基本相同的解决方案。
Caffeine is my main source of energy, due to the fact that I spend most of my life half-asleep as I spend far too much time coding; thus I didn't realise my faux-pas until you rightly pointed it out.
咖啡因是我的主要能量来源，因为我花了太多时间编码，所以我大部分时间都在半睡半醒；因此我没有意识到我的失礼，直到你正确指出。
Therefore, for the record, I prefer your second approach: using an SP to raise the current error, and then using a TRY/CATCH around your parameter validation.
因此，作为记录，我更喜欢您的第二种方法：使用 SP 来引发当前错误，然后围绕您的参数验证使用 TRY/CATCH。
It reduces the need for all the IF/BEGIN/END blocks and therefore reduces the line count as well as puts the focus back on the validation. When reading through the code for the SP it's important to be able to see the tests being performed on the parameters; all the extra syntactic fluff to satisfy the SQL parser just gets in the way, in my opinion.
它减少了对所有 IF/BEGIN/END 块的需求，因此减少了行数并将重点重新放在验证上。在阅读 SP 的代码时，重要的是能够看到对参数执行的测试；在我看来，所有满足 SQL 解析器的额外语法错误都会妨碍到它。
回答by Dalex
I always use parameter @Is_Success bit as OUTPUT. So if I have an error then @Is_success=0. When parent procedure checks that @Is_Success=0 then it rolls back its transaction(with child transactions) and sends error message from @Error_Message to client.
我总是使用参数@Is_Success 位作为输出。所以如果我有错误，那么@Is_success=0。当父过程检查@Is_Success=0 时，它会回滚其事务（带有子事务）并将错误消息从@Error_Message 发送到客户端。
回答by KM.
I prefer to return out as soon an possible, and see not point to having everything return out from the same point at the end of the procedure. I picked up this habit doing assembly, years ago. Also, I always return a value:
我更喜欢尽快返回，并且认为不要在程序结束时从同一点返回所有内容。几年前，我在组装时养成了这个习惯。另外，我总是返回一个值：
RETURN 10
The application will display a fatal error on positive numbers, and will display the user warning message on negative values.
应用程序将在正数上显示致命错误，并在负数上显示用户警告消息。
We always pass back an OUTPUT parameter with the text of the error message.
我们总是将带有错误消息文本的 OUTPUT 参数传回。
example:
例子：
IF ~error~
BEGIN
--if it is possible to be within a transaction, so any error logging is not ROLLBACK later
IF XACT_STATE()!=0
BEGIN
ROLLBACK
END
SET @OutputErrMsg='your message here!!'
INSERT INTO ErrorLog (....) VALUES (.... @OutputErrMsg)
RETURN 10
END
