Conditional WHERE clauses in T-SQL using comparison operators

Ever had a query where you wished you could be able to specify the operator (equal, not equal, greater than, etc.) for each column contained in your WHERE clause, without having to use ugly string concatenations and the infamous EXEC keyword? Here we'll see an example of how this can be achieved with the use of a plain SQL query together with some CASE statements.