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.

Something I see often is difficulty in using “filters” in a stored procedure used to select – for example – products based on various different restrictions. Often I see people using dynamically written SQL in the code-layer and then sent to the SQL Server and EXEC.

However a different method of doing this is to utilize the logic of the OR functionality in the Stored Procedure.

Suppose you have a table containing products. These products have – for example – a weight column, height, length and other such physical attributes. Now suppose you want to select all products from this table, but also give the option to filtering the select further – say below a specific weight and/or below a specific length and so on.
This is doable the following way:

  DECLARE @Weight INT = NULL
  DECLARE @Length INT = NULL
  DECLARE @Width INT = NULL
  
  SELECT * 
  FROM Product
  WHERE (@Weight IS NULL OR (ProductWeight <= @Weight))
  AND (@Length IS NULL OR (ProductLength <= @Length))
  AND (@Width IS NULL OR (ProductWidth <= @Width))  

Note this is 2008 syntax, if using 2000 or 2005 remember you can’t assign value to a declaration in the same line.

Also if using it in a Stored Procedure, the declarations are usually input parameters – but just default them to (for example) NULL.

When running this, with the null assignment, you’ll get all rows out, because all rows will fulfill the first part of the OR logic.

However if changing one, or more, of the variables to an actual value, the SQL will filter based on those values, because – as the variables no longer hold the value “null”, the second part of the OR statement comes into play.
So suppose you have the following values instead:

  DECLARE @Weight INT = 10
  DECLARE @Length INT = 15
  DECLARE @Width INT = 20  

The select will only select out those product rows which have a Weight less than or equal to 10, a Length equal to or less than 15 and likewise with Width and 20.
Omit one value and you’ll not filter for that one either, so 

  DECLARE @Weight INT = NULL
  DECLARE @Length INT = 10
  DECLARE @Width INT = NULL  

Will select all products which have a length less than or equal to 10 – regardless of Weight and Width.

Of course, me using <= is basically irrelevant, as any logical operation can be performed within the second part of the OR statement.

An inherit disadvantage to this type of procedure over the more specialized ones are the difficulty in utilizing indexes. Due to the OR nature of the procedure, you’ll be unable to utilize the indexes optimal, so it is naturally a concern which you must consider when using such approach.

Occasionally I have the problem that I need to run as query with a form of dynamic sorting - meaning sometimes ascending or descending and sometimes based on an ordernumber or a date, for example.

Now a CASE statement in the order by is often very effective for handeling such issues, but I've not found it possible to combine it with the Ascending and Descending clasuses (yet, could be that I'm just doing it wrong). 

For example suppose we have a table which contains a "Date" field of the type DateTime, and we need to be able to sort this both descending and ascending based on some input parameter. Instead of making two queries, one might think that something like this would work

DECLARE @ASC BIT
SET @ASC = 1
SELECT *
FROM DateTimeOrderTest
ORDER BY DATE
    CASE @ASC
        WHEN 1 THEN ASC
        ELSE DESC
    END

But well - it doesn't: "Incorrect syntax near the keyword 'CASE'."

Or one might think:

DECLARE @ASC BIT
SET @ASC = 1
SELECT *
FROM DateTimeOrderTest
ORDER BY
    CASE @ASC
        WHEN 1 THEN Date ASC
        ELSE Date DESC
    END

Which gives a nice little: Incorrect syntax near the keyword 'ASC'

A little fix to get around this would be to not utilize case, but instead make the Ascending and Descending a factor so when Ascending you have a factor of 1 and Descending you have a factor of -1. Then simply convert/cast the Date to an integer (which should not be a terrible expensive procedure, as the datetime is represented as a number anyway, I wonder if it is possible to somehow use the internal representation for my purpose, or if the database does that when casting as integer....oh, well), and multiply the factor to the order by.

This mean the following two queries give the same result:

SELECT  *
FROM DateTimeOrderTest
ORDER BY CAST(Date AS INT) * -1

SELECT *
FROM DateTimeOrderTest
ORDER BY Date DESC

This technique of multiplying either 1 or -1, can be used in many queries, and of course is not limited to datetimes.

Anyways, I've found it quite useful myself.