Optional parameters are a pretty critical part of stored procedure writing. You won't get too far into report writing before users start asking for filters on their reports. In general it's a good idea to send as little data "across the wire" as possible for performance reasons (although you can filter data sets in Reporting Services after the fact as well). So how do you add an optional parameter to a stored procedure? Well, let's set up some example data. I have a table called "VideoGames" that has the following data in it.

Now let's talk about our objective: We want to write a procedure that is capable of returning the "GameTitle" and "GameSystem" columns in our "VideoGame" table. We also want our procedure to have an optional parameter called @p_GameSystem that returns only matching results for a Game System if a value is passed into it. If nothing gets passed into @p_GameSystem we want no filter to be applied to our procedure.

Ok let's start with a simple procedure that does almost everything we want it to, but the @p_GameSystem is not optional, and must always passed in when calling the procedure.

CREATE PROCEDURE usp_OpParamExample

@p_GameSystem VARCHAR(50)

AS

BEGIN --proc

      SELECT

            vg.GameTitle,

            vg.GameSystem

      FROM

            VideoGames vg

      WHERE

            vg.GameSystem = @p_GameSystem      

END --proc 

The first thing we need to do in order to make our @p_GameSystem parameter optional is to give it a default value. We can accomplish that by simply putting an = NULL after its declaration like so:

@p_GameSystem VARCHAR(50) = NULL

The next thing well want to do is change the WHERE clause to behave differently based on the value of @p_GameSystem . To do that, we're going to change its comparison to the following:

      WHERE

            (@p_GameSystem IS NULL OR vg.GameSystem = @p_GameSystem)   

Let's examine how this statement will evaluate for each of its two possible scenarios:

1. Nothing is passed into @p_GameSystem when the procedure is called: Under this scenario the first part of our WHERE statement (@p_GameSystem IS NULL) will always evaluate to true. Because we used "OR" between our two conditionals, only one of the statements needs to be true in order for the row to be returned. So every row in our table will be returned.

2. Something is passed into @p_GameSystem when the procedure is called: Under this scenario, the first test (@p_GameSystem IS NULL) will always be false, however our second condition (vg.GameSystem = @p_GameSystem) will potentially be true, assuming we've passed in a value that exists in our table. Because we used "OR" between our two conditionals, only one of the statements needs to be true in order for the row to be returned. So only rows where (vg.GameSystem = @p_GameSystem) will be returned.

That's it! We now have a procedure that allows a parameter to be optionally passed in. The entire procedure would now look like this:

CREATE PROCEDURE usp_OpParamExample

@p_GameSystem VARCHAR(50) = NULL

AS

BEGIN --proc

      SELECT

            vg.GameTitle,

            vg.GameSystem

      FROM

            VideoGames vg

      WHERE

            (@p_GameSystem IS NULL OR vg.GameSystem = @p_GameSystem)   

END --proc 

Now there are a few other ways we could have implemented this. Two common alternates to the WHERE statement would be either using the ISNULL operator like so:

WHERE

      vg.GameSystem = ISNULL(@p_GameSystem, vg.GameSystem)

or using COALESCE like so:

WHERE

      vg.GameSystem = COALESCE(@p_GameSystem, vg.GameSystem)

While I must admit both of these alternatives are way easier to read, It's my experience that neither of them perform as well as the method we used in our example, particularly against large sets, or where we have multiple optional parameters in the same query. Hope this helps.