Using optional parameters within a stored procedure is a good way to give the caller a little more control over what is being returned.

For example, if you were writing a stored procedure which was going to be used by a reporting system, and within the system, a user will have the ability to narrow down a search by choosing options from various drop down lists. Instead of writing many different stored procedures to handle each of the different drop down lists, you can do it all within one using optional parameters.

An “optional parameter” is a parameter which is set to NULL by default.

CREATE PROCEDURE dbo.GetSearchResults
 
@Franchise	VARCHAR(20) = NULL
@Make		VARCHAR(20) = NULL
@Colour		VARCHAR(20) = NULL
 
AS
 
SELECT Franchise,
       Make,
       Colour
 
FROM Vehicles
 
WHERE (@Franchise IS NULL OR Franchise = @Franchise) AND
      (@Make IS NULL OR Make = @Make) AND
      (@Colour IS NULL OR Colour = @Colour)

In the above example, if a value isn’t passed in for one of the parameters, then the default of NULL is used. This means that the first condition in the WHERE for that parameter (The “@Franchise IS NULL”, for example) is true because the variable is NULL, and execution continues on to the next line.

The above stored procedure can also be called in a few different ways, because of the added flexibility gained by using optional parameters:

1)

EXEC dbo.GetSearchResults

This method simply uses all of the default values for each parameter, and the query results aren’t filtered in the where clause at all

2)

EXEC dbo.GetSearchResults 'Volkswagen', 'Golf'

This execution method uses the values passed in and assigns them to the parameter in the same position that the parameter is declared. By that I mean, @Franchise is declared first in the procedure, so Volkswagen would be assigned to this variable as it’s passed in first. This method is a little dangerous, as if the value intended for @Colour is passed in first, no results will be returned.

3)

EXEC dbo.GetSearchResults @Franchise = 'Volkswagen', @Colour = 'Red'

This method is probably the safest of the bunch, as it explicitly defines the value to the parameter. Any parameters that aren’t defined in the EXEC use the default value set in the procedure.