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)
    RETURN
END

IF (ISNULL(@fooString, '') = '')
BEGIN
    RAISERROR('Invalid parameter: @fooString cannot be NULL or empty', 18, 0)
    RETURN
END