Using Optional Stored Procedure Parameters

A reader recently wrote to me asking about how to check for a missing or invalid parameter inside a stored procedure. I’m sure other developers have had the same requirement, so I thought I’d share my answer in this short article.

There isn’t much to it really, but there is one trick you need to know about. That trick is to give the incoming parameter a default value, like this:

CREATE PROCEDURE [dbo].[pTestParam]
   @MyNumber INT = NULL
AS ...

Optional versus Required Parameters

Giving the parameter a default value effectively makes it an optional stored procedure parameter.

If you don’t give the parameter a default value, the parameter is a "required" parameter, and SQL Server raises an error in any calling procedure that fails to provide a value for the parameter. In other words, with required parameters, SQL Server steps in before your procedure gets a chance to evaluate any parameter values.

If all you want to do is raise an error when you detect a missing parameter, you are really just postponing the inevitable: either way the calling procedure is going to get back an error. But what you get out of deal could be very important: control over when and how the error is reported.

Here’s a simple example of how you might check for a missing or invalid parameter:

   IF (@MyNumber < 1) OR (@MyNumber IS NULL) BEGIN
      /* handle the error */
   END

What you do to handle the situation is completely up to you.

Unfortunately, there’s no way to differentiate between when the parameter is truly "missing" versus when the calling procedure is passing the default value. That is one reason I tend to use NULL as the default value. Even if the calling procedure passes NULL, it is literally saying "I’m not passing a value for this parameter."

Scenarios for Using Optional Parameters

Using optional parameters can come in handy for a number of situations:

• When a parameter is missing, assign it a default value. You could do that through the parameter declaration itself (e.g. @MyNumber INT = 1), or you might use a complex algorithm within the procedure to select the default value.
• When a parameter is missing, return a specific error code that has special meaning to the calling procedure, so it can take appropriate action. My example procedure below demonstrates this approach.
• When a parameter is missing, branch to specific logic within the stored procedure.

I’m sure you can think of more situations where optional parameters might come in handy. After all, you probably use the concept all the time in your application code, and a stored procedure is really nothing more than application code embedded in the database.

Sample Stored Procedure

Here’s the stored procedure that I sent back to my reader to demonstrate using and evaluating optional parameters:

CREATE PROCEDURE [dbo].[pTestParam]
   @MyNumber INT = NULL
AS BEGIN
   DECLARE @ReturnCode int
   DECLARE @Message varchar(200)
 
   SET @Message = '[pTestParam] '
   SET @ReturnCode = 0
 
   IF (@MyNumber < 1) OR (@MyNumber IS NULL) BEGIN
      SET @Message = @Message +
   '@MyNumber parameter must be greater than 0.'
      SET @ReturnCode = 1
      GOTO ProcError
   END
 
ProcExit:
   RETURN(@ReturnCode)
ProcError:
   /* Raise errors detected in this proc. */
   RAISERROR(@Message, 16, 1)
   GOTO ProcExit
END

You can call the procedure without a parameter and capture the return code like this:

declare @rc int
exec @rc = [dbo].[pTestParam]
print '@rc = ' + cast(@rc as varchar)

Here’s what you get back from SQL Server:

Msg 50000, Level 16, State 1, Procedure pTestParam, Line 21
[pTestParam] @MyNumber parameter must be greater than 0.
@rc = 1

If you pass a valid parameter, execution completes successfully and returns a zero return code.

If you want a more detailed explanation of the error handling technique I’m using in this example, you’ll find it in my Coding Transactions in Transact-SQL article here on Nerdy Musings.

Conclusion

One thing to remember about Transact-SQL is that it is just another programming language. If you are used to doing something in one language, there’s a good chance you can do a similar thing in another, with optional parameters being just one example. After all, all programming languages have the same basic problems to solve, even if they are optimized to operate in a specific environment.