title: The "right" way to do stored procedure parameter validation
body: <p>I have a stored procedure that does some parameter validation and should fail and stop execution if the parameter is not valid.</p>
  
  <p>My first approach for error checking looked like this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>This didn't do the trick since severity 18 doesn't stop the execution and 'validation succeeded' is printed together with the error messages.</p>
  
  <p>I know I could simply add a return after every raiserror but this looks kind of ugly to me:</p>
  
  <pre><code>  if (@fooInt = 0 and (@fooString is null or @fooString = ''))
    begin
      raiserror('invalid parameter: foo', 18, 0)
      return
    end
  
    ...
  
    print 'validation succeeded'
    -- do some work
  </code></pre>
  
  <p>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 <a href="http://sqlblog.com/blogs/roman_rehak/archive/2007/12/01/how-to-rethrow-errors-in-t-sql.aspx" rel="noreferrer">rethrow</a> the error:</p>
  
  <pre><code>  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
  </code></pre>
  
  <p>I'm still not happy with this approach so I'm asking you:</p>
  
  <p>How does your parameter validation look like? Is there some kind of "best practice" to do this kind of checking?</p>