The "right" way to do stored procedure parameter validation

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)    RETURNENDIF (ISNULL(@fooString, '') = '')BEGIN    RAISERROR('Invalid parameter: @fooString cannot be NULL or empty', 18, 0)    RETURNEND

We normally avoid raiseerror() and return a value that indicates an error, for example a negative number:

if <errorcondition>    return -1

Or pass the result in two out parameters:

create procedure dbo.TestProc    ....    @result int output,    @errormessage varchar(256) outputasset @result = -99set @errormessage = null....if <errorcondition>    begin    set @result = -1    set @errormessage = 'Condition failed'    return @result    end

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.

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.