Rather than repeat the same logic in
multiple places, we need to refactor the common functionality out of our
two stored procedures. We can implement the definition of "sales for a
given month" in an inline UDF, as shown in Listing 1.
This new inline UDF can then be used in both stored procedures.
Can we reuse the definition of the reporting period
in other queries against other tables? We can at least try to go one
step further and have one module define our reporting period and do
nothing else. As usual, we should verify that the performance is still
acceptable. The code in Listing 3 shows how to implement the definition of reporting period as an inline UDF.
We can utilize this inline UDF when we implement the "sales for a given month" functionality.
Alternatively, we can use a scalar UDF to implement the definition of reporting period, as shown in Listing 5.
We also have to change our SalesForMonth function, so that it utilizes our new scalar UDF, as shown in Listing 6.
Note that the new implementation of SalesForMonth is simpler than the previous one): instead of using the CROSS APPLY clause to utilize the inline UDF, we can just invoke the scalar UDF directly in the WHERE clause.
The basic fact remains, however, that implementing
the same logic in multiple places increases the possibility of bugs when
our requirements change. Instead, we should aim for sensible code reuse
wherever possible, and UDFs are just one of the means to achieve this.
Over the coming sections, we'll discuss
other ways in which we can reuse T-SQL code, as dictated by the given
circumstances. Overall, reusing code is a very important component of
defensive programming, and I cannot emphasize strongly enough how much
it can improve the robustness of our code.
