# Reduce the code in a WHERE clause without using dynamic SQL

How can I write the following T-SQL query part in a shorter way without using dynamic SQL?

WHERE
( (@Max IS NULL OR @Type <> 'Products')
OR (@Max IS NOT NULL AND @Type = 'Products'
AND ProductCount >  @Max ) )

AND ( (@Min IS NULL OR @Type <> 'Products')
OR (@Min IS NOT NULL AND @Type = 'Products'
AND ProductCount <  @Min ) )

AND ( (@Max IS NULL OR @Type <> 'Vendors')
OR (@Max IS NOT NULL AND @Type = 'Vendors'
AND VendorCount >  @Max ) )

AND ( (@Min IS NULL OR @Type <> 'Vendors' )
OR (@Min IS NOT NULL AND @Type = 'Vendors'
AND VendorCount <  @Min ) )

AND ( (@Max IS NULL OR @Type <> 'Order')
OR (@Max IS NOT NULL AND @Type = 'Order'
AND OrderCount >  @Max ) )

AND ( (@Min IS NULL OR @Type <> 'Order')
OR (@Min IS NOT NULL AND @Type = 'Order'
AND OrderCount <  @Min ) )

• By "shorter", do you mean "shorter" in terms of text length, or in terms of efficiency? The first really doesn't matter-long queries can be efficient and short ones inefficient. Is the query running poorly for you?
– Todd
Jun 1 '11 at 6:13
• @Todd No performance issue with current query, i just want make it "shorter" in terms of text length Jun 1 '11 at 6:15
• maybe because you are not using it? it's definition is for posting code for peer review. Jun 1 '11 at 6:28
• Maybe your question will inspire some people to visit the site slightly more often, and some others to come up with further interesting questions. :) Jun 1 '11 at 7:37
• What is the purpose of the Query? What does the rest of the query look like?
– Malachi
Jul 21 '14 at 15:03

You will have to test this carefully, but the following query should work:

WHERE
(
@Max IS NULL
OR @Type = 'Products' AND ProductCount > @Max
OR @Type = 'Vendors'  AND VendorCount  > @Max
OR @Type = 'Order'    AND OrderCount   > @Max
)
AND
(
@Min IS NULL
OR @Type = 'Products' AND ProductCount < @Min
OR @Type = 'Vendors'  AND VendorCount  < @Min
OR @Type = 'Order'    AND OrderCount   < @Min
)


Something like this

You can rely on the NULL comparison to always give false (strictly: unknown) if @Max or @Min is NULL for the relevant CASE

WHERE
CASE @Type
WHEN 'Products' THEN ProductCount
WHEN 'Vendors' THEN VendorCount
WHEN 'Order' THEN OrderCount
END > @Max
OR
CASE @Type
WHEN 'Products' THEN ProductCount
WHEN 'Vendors' THEN VendorCount
WHEN 'Order' THEN OrderCount
END < @Min

• Pedant's corner: the NULL comparison gives UNKNOWN, not FALSE. putting a NOT in front of it will still exclude those rows.
– Damien_The_Unbeliever
Jun 1 '11 at 8:21
• @Damien_The_Unbeliever: yes, yes, I thought about that. But didn't want to deprive anyone of their fun ;-)
– gbn
Jun 1 '11 at 8:23

Here's another stab at it, based on gbn's CASE idea, but using BETWEEN to avoid repeating the cases:

WHERE
CASE @Type
WHEN 'Products' THEN ProductCount
WHEN 'Vendors' THEN VendorCount
WHEN 'Orders' THEN OrderCount
END BETWEEN IFNULL(@Min,0) AND IFNULL(@Max,99999999)


Note: IFNULL in MySQL should be replaced by ISNULL in TSQL

I am just going to step through this so that it is a little easier to understand for someone happening upon this question in the future.

This is what we are starting with

WHERE
( (@Max IS NULL OR @Type <> 'Products')
OR (@Max IS NOT NULL AND @Type = 'Products'
AND ProductCount >  @Max ) )

AND ( (@Min IS NULL OR @Type <> 'Products')
OR (@Min IS NOT NULL AND @Type = 'Products'
AND ProductCount <  @Min ) )

AND ( (@Max IS NULL OR @Type <> 'Vendors')
OR (@Max IS NOT NULL AND @Type = 'Vendors'
AND VendorCount >  @Max ) )

AND ( (@Min IS NULL OR @Type <> 'Vendors' )
OR (@Min IS NOT NULL AND @Type = 'Vendors'
AND VendorCount <  @Min ) )

AND ( (@Max IS NULL OR @Type <> 'Order')
OR (@Max IS NOT NULL AND @Type = 'Order'
AND OrderCount >  @Max ) )

AND ( (@Min IS NULL OR @Type <> 'Order')
OR (@Min IS NOT NULL AND @Type = 'Order'
AND OrderCount <  @Min ) )


I am going to first take out all the <> conditions because we are already checking in the or statements for @Type = {type} and I am going to take out the check for @Max IS NOT NULL because if it were NULL we wouldn't hit that condition anyway.

WHERE
( @Max IS NULL
OR (@Type = 'Products'
AND ProductCount >  @Max ) )

AND ( @Min IS NULL
OR (@Type = 'Products'
AND ProductCount <  @Min ) )

AND ( @Max IS NULL
OR (@Type = 'Vendors'
AND VendorCount >  @Max ) )

AND ( @Min IS NULL
OR (@Type = 'Vendors'
AND VendorCount <  @Min ) )

AND ( @Max IS NULL
OR (@Type = 'Order'
AND OrderCount >  @Max ) )

AND ( @Min IS NULL
OR (@Type = 'Order'
AND OrderCount <  @Min ) )


now we have @Max IS NULL and @Min IS NULL checks that we could combine so we aren't repeating ourselves.

WHERE
(
@Max IS NULL
OR
(@Type = 'Products' AND ProductCount >  @Max)
OR
(@Type = 'Vendor' AND VendorCount > @Max)
OR
(@Type = 'Order' AND OrderCount > @Max)
)
AND
(
@Min IS NULL
OR
(@Type = 'Products' AND ProductCount < @Min)
OR
(@Type = 'Vendor' AND VendorCount < @Min)
OR
(@Type = 'Order' AND OrderCount < @Min)
)


This is the Final Solution that @Peter Lang came to. I use Parenthesis to make sure that the where clause is being interpreted by the RDBMS the way that I want them interpreted, if they aren't interpreted the way I think they will be it can lead to weird results that sometimes are hard to spot.

Always double check your returned data to make sure you are getting what you want.