Reusable code

My definition of reusable code: modular code that is flexible enough that it can be used in more than one way.

For example, let's say that you have a table Orders like this:

ID int identity,
OrderDate datetime,
Quantity int

and you want the total orders for one year. Fine. All you need is something like this:

CREATE PROC Test
(
@Year int
)
AS
SELECT SUM(Quantity) AS Total
FROM Orders
WHERE
YEAR(OrderDate) = @Year

which answers the immediate need.

But if you write it like this, you can execute the sp without a parameter & get the current year:

CREATE PROC Test
(
@Year int = 0
)
AS
SELECT SUM(Quantity) AS Total
FROM Orders
WHERE
YEAR(OrderDate) =
CASE
WHEN @Year = 0
THEN YEAR( GETDATE() )
ELSE @Year END

Another approach is to add a 2nd parameter for the month, like this:

CREATE PROC Test
(
@Year int = 0,
@Month int = 0
)
AS
SELECT SUM(Quantity) AS Total
FROM Orders
WHERE
YEAR(OrderDate) =
CASE
WHEN @Year = 0
THEN YEAR( GETDATE() )
ELSE @Year END
AND
(
MONTH(OrderDate) = @Month
OR
@Month = 0
)

which allows you specify a single month, or use a zero to get the whole year.

Granted, these are very basic examples, but if you build in little hooks like these at the outset, you can save time later on.