进行存储过程参数验证的“正确”方法。

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 rethrow the error:

由于严重程度为11或更高的错误被捕获在try/catch块中，因此我测试的另一种方法是将错误检查封装在这样的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?

您的参数验证是怎样的?是否存在某种“最佳实践”来进行这种检查?

5 个解决方案

#1

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

#2

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:

或将结果传入两个out参数:

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

#3

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解析器的所有额外的语法错误都妨碍了它。

#4

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.

我们总是用错误消息的文本传递输出参数。

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

#5

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发送错误消息给客户端。

#1