One of the problems I often encounter in SQL when reading forums, solving problems for myself and so on, are equality and inequality checks and the need to make them dynamic.
A rather nifty (in my view) mathematical trick can be used combining the checks > with < and >= with <= in one query without the use of IF or CASE.

Note that logically when A is greater than B, then –A will be less than –B, and that is the core of the logic which is behind this.

Suppose I have a table (MyTable) containing numerical values in a Value1 field, which I need to check against an arbitrary number – I’ll use 25 in my example. Then if I write the following code:

DECLARE @modifier INT = 1

SELECT *
FROM MyTable 
WHERE Value1 * @modifier > 25 * @modifier
ORDER BY Value1 DESC
(Note this is 2008 syntax, if using 2005 or ealier, just move the assignment of the modifier variable to another line)

In this case when the modifier is 1 the check will be Value1 > 25, but if I change the modifier value to -1, the logical check will now effectively be Value1 < 25. So just to recap - with a modifier of 1, I'd get all rows where Value1 is greater then 25, and with a modifier of -1 I would get the rows where Value1 is less then 25.

And if I needed to check for <= or >= all I’d have to do is apply a second check in an OR clause with a second modifier.

DECLARE @modifier INT = -1
DECLARE @modifier2 INT = 1

SELECT *
FROM MyTable 
WHERE Value1 * @modifyer > 25 * @modifyer
OR Value1 * @modifier2 = 25 
ORDER BY value1 DESC

Now this time around, if my @modifier2 is 1 the check now includes the equal check (so 25 will also be selected out), but if I change it to 0, the equal check is no longer present, and it’ll function as the earlier one without the second check.
So this means I can now check for > and < and >= and <= simply by changing @modifier between 1 and -1 and @modifier2 between 0 and 1. And by applying another modifier to value1 in the ORDER BY I could change between ASC and DESC on the fly as mentioned in my blog post about Dynamic ASC and DESC and Order by DateTime.

The main problem here is of course the difficulty in using possible indexes.
So if you have a large table it might hurt performance too much, and multiple stored procedures are the better approach.
But as always it is a case by case judgment call.  In my small testing for this piece it was about a 62/38 ratio meaning the mathematical dynamic approach took 62% and a specialized optimized query took 38%.
However I would need to maintain 4 different functions either via different queries, IF-constructs or CASE-constructs to get the same dynamic.
And of course the specialized and optimize will perform better, but at times the performance hit is negligible and at times a more dynamic approach is more useful.