Conditional WHERE clauses in T-SQL using comparison operators
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.
The Scenario
We will use the Customers table from the sample Northwind database for our example. Let's suppose you want to query the Customers table for the following:
- All records that contain the word "the" in the company name
- All records for companies located in Germany, excluding companies starting with the letter "A"
Normally, you could create a dynamic statement consisting of multiple IF statements and strings to be concatenated to a final variable which should be executed with the use of the EXEC keyword. But in some cases this is not desirable, and you would like something more versatile (although maybe slightly less efficient).
The code
Let's see how we can form a query to serve the above specs. Take a look at this code. Be sure to read the comments included in the code for an explanation of what is happening.
-- Declare some local variables. Actually, we are creating a pair of variables
-- for each column included in our WHERE clause.
-- The first variable represents the value we are filtering and the second
-- represents the "operator" for the filter.
declare @companyName varchar(255)
declare @companyNameOp varchar(2)
declare @country varchar(255)
declare @countryOp varchar(2)
-- Let's set some sample values now. The values you see here represent the second
-- of the two scenarios described above, i.e. all records for companies located in Germany,
-- excluding companies starting with the letter A
-- Operators are defined here with arbitrary, two-letter values.
-- Of course you could define your own set of operators, with different
-- naming conventions. For our example, here's the meaning of each possible
-- value:
-- ne = not equal
-- eq = equal
-- bg = begins with
-- ed = ends with
-- ct = contains
-- For our example, we are using only varchar fields in our WHERE clause.
-- It is very easy, though, to define operators for other data types as well.
set @companyname = 'A%'
set @companynameOp = 'ne'
set @country = 'Germany'
set @countryOp = 'eq'
-- Ok, now let's form our query.
select
customerid, companyname, country
from
customers
where
case @companyNameOp
when '' then 1 -- Operator not defined, get everything
when 'eq' then -- Operator is "equals"
case when companyname like @companyName then 1 else 0 end
when 'bg' then -- Operator is "begins with"
case when companyname like @companyName +'%' then 1 else 0 end
when 'ed' then -- Operator is "ends with"
case when companyname like '%' + @companyName then 1 else 0 end
when 'ct' then -- Operator is "contains"
case when companyname like '%' + @companyName +'%' then
1 else 0 end
when 'ne' then -- Operator is "not equal"
case when companyname not like @companyName then 1 else 0 end end =1
AND
-- Same approach for the second field
case @countryOp
when '' then 1
when 'eq' then
case when country like @country then 1 else 0 end
when 'bg' then
case when country like @country +'%' then 1 else 0 end
when 'ed' then
case when country like '%' + @country then 1 else 0 end
when 'ct' then
case when country like '%' + @country +'%' then 1 else 0 end
when 'ne' then
case when country not like @country then 1 else 0 end
end =1
Conclusion
The conditional WHERE clauses are based on the simple principle defined by the query "SELECT something FROM sometable WHERE 1=1" As you can see, all CASE statements evaluate to either 1 or 0, so the comparison with 1 can either be false or true for each row.
Of course, you can define your own set of operators (like operators for numeric values) and you can extend your queries to include more fields. The query, as defined here, lets you also NOT define an operator, meaning that nothing will be filtered by the specific field connected to the operator.
Please note that this article serves only as a starting point. You may need to put extra effort (and add extra functionality) in case something like this is required in a production environment, but in my personal opinion, this approach is particularly useful when dealing with customizable query wizards or similar stuff in applications, especially when some kind of custom reporting is involved. Such queries can be easily transformed to stored procedures with all parameters optional and, having some additional checks, return resultsets filtered only by the parameters (and operators) given each time.
