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:
@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
