Dynamic WHERE clauses for SQL queries

Let's assume that the following example query is needed for getting the number or orders and the sum of order values from some tables:

select count(orders.orderid), sum(orders.value)
from orders
join customers on orders.customerid = customers.customerid
join stores on orders.storeid = stores.storeid
join cities on customers.cityid = cities.cityid
join products on orders.productid = products.productid
where orders.value > 0
and stores.type = 'wholesale'
and customers.id is not null
and cities.name is not null
and products.type = 'fruit'
and orders.value > 1000000

The query uses only data from wholesale stores, fruit products and orders with value greater than 1000000.

Since it is possible that this query will run for different parameter values, let's change it in a stored procedure:

create procedure getInfo
@StoreType as varchar(50),
@ProductType as varchar(50)

as

begin

select count(*), sum(value)
from orders
join customers on orders.customerid = customers.customerid
join stores on orders.storeid = stores.storeid
join cities on customers.cityid = cities.cityid
join products on orders.productid = products.productid
where orders.value > 0
and stores.type = @StoreType
and customers.id is not null
and cities.name is not null
and products.type = @ProductType
and orders.value > 1000000

end

Having the stored procedure, it is very easy to run it

declare @StoreType as varchar(50) = 'wholesale'
declare @ProductType as varchar(50) = 'fruit'

exec getInfo @StoreType, @ProductType



Let's assume now that for a certain product type (vegetable), we need a very similar query that has the operator in the last where clause different (< instead of >):

select count(*), sum(value)
from orders
join customers on orders.customerid = customers.customerid
join stores on orders.storeid = stores.storeid
join cities on customers.cityid = cities.cityid
join products on orders.productid = products.productid
where orders.value > 0
and stores.type = 'wholesale'
and customers.id is not null
and cities.name is not null
and products.type = 'vegetable'
and orders.value < 1000000


Since it is not a good idea to create another stored procedure, lets see how we can change the existing one:

alter procedure getInfo
@StoreType as varchar(50),
@ProductType as varchar(50)

as

begin

if @ProductType = 'fruit'

select count(*), sum(value)
from orders
join customers on orders.customerid = customers.customerid
join stores on orders.storeid = stores.storeid
join cities on customers.cityid = cities.cityid
join products on orders.productid = products.productid
where orders.value > 0
and stores.type = @StoreType
and customers.id is not null
and cities.name is not null
and products.type = @ProductType
and orders.value > 1000000

else
if @ProductType = 'vegetable'

select count(*), sum(value)
from orders
join customers on orders.customerid = customers.customerid
join stores on orders.storeid = stores.storeid
join cities on customers.cityid = cities.cityid
join products on orders.productid = products.productid
where orders.value > 0
and stores.type = @StoreType
and customers.id is not null
and cities.name is not null
and products.type = @ProductType
and orders.value < 1000000

else

return


end


This works but it is not very good.

What happens if we will have yet another special case as follows?

select count(*), sum(value)
from orders
join customers on orders.customerid = customers.customerid
join stores on orders.storeid = stores.storeid
join cities on customers.cityid = cities.cityid
join products on orders.productid = products.productid
where orders.value > 0
and stores.type = 'wholesale'
and customers.id is not null
and cities.name is not null
and products.type = 'herb'
and orders.value < 1000000
and orders.paid > 1000000

The stored procedure will become very complicated with lots of conditional statements and almost identical queries.



What is needed is a way of selecting the correct clause depending on the value of the Product Type parameter.

Please see below the final version of the stored procedure:


alter procedure getInfo
@StoreType as varchar(50),
@ProductType as varchar(50)

as

begin

select count(*), sum(value)
from orders
join customers on orders.customerid = customers.customerid
join stores on orders.storeid = stores.storeid
join cities on customers.cityid = cities.cityid
join products on orders.productid = products.productid
where orders.value > 0
and stores.type = @StoreType
and customers.id is not null
and cities.name is not null
and products.type = @ProductType
and
(
(isnull(@ProductType, 0) = 'fruit' and orders.value > 1000000)
OR
(isnull(@ProductType, 0) = 'vegetable' and orders.value < 1000000)
OR
(isnull(@ProductType, 0) = 'herb' and orders.value < 1000000 and orders.paid > 1000000)
)

end

Using the new technique, the stored procedure remains compact and efficient.

Why does this work?

This new clause uses the rules of Boolean algebra.

Lets see how the condition evaluates for @ProductType = 'fruit':

(ISNULL('fruit', 0) = 'fruit' AND orders.value > 1000000)
OR
(ISNULL('fruit', 0) = 'vegetable' AND orders.value < 1000000)
OR
(ISNULL('fruit', 0) = 'herb' AND orders.value < 1000000 and orders.paid > 1000000)

becomes

'fruit' = 'fruit' AND orders.value > 1000000
OR
'fruit' = 'vegetable' AND orders.value < 1000000)
OR
'fruit' = 'herb' AND orders.value < 1000000 and orders.paid > 1000000)

becomes

true AND orders.value > 1000000
OR
false AND orders.value < 1000000)
OR
false AND orders.value < 1000000 and orders.paid > 1000000)

becomes

true AND orders.value > 1000000
OR
false
 OR
false

becomes

true AND orders.value > 1000000 

becomes

orders.value > 1000000

The condition is evaluated similarly for the other values of the parameters.

Share this post