Surviving Changes to the Signature of a Stored Procedure

Consider the stored procedure shown in Listing 1, SelectCustomersByName, which takes two optional search conditions, and selects data from the Customers table, as defined in Listing 1.

Listing 1. The SelectCustomersByName stored procedure.

When we invoke this stored procedure, we can explicitly name its parameters, and make the code more readable, but we are not forced to do so, as shown in Listing 2.

Listing 2. Two ways to invoke the SelectCustomersByName stored procedure.

At the moment, either way of invoking the stored procedure produces the same result. Suppose, however, that the signature of this stored procedure is subsequently modified to accept an optional @FirstName parameter, as described in Listing 3.

Listing 3. The modified SelectCustomersByName stored procedure includes an additional FirstName parameter.

As a result of this modification, the two ways of invoking the stored procedure are no longer equivalent. Of course, we will not receive any error message; we will just silently start getting different results, as shown in Listing 4.

Listing 4. The same stored procedure call is interpreted differently after the signature of that stored procedure has changed.

The lesson here is clear: stored procedure calls with explicitly named parameters are more robust; they continue to work correctly even when the signature of the stored procedure changes, or they give explicit errors instead of silently returning incorrect results.