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.
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.