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.
I know I could simply add a return after every raiserror but this looks kind of ugly to me:
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:
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?
Best Answer
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.