Reduce the code in a WHERE clause without using dynamic SQL

Question

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 ) )

• Todd: 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?
• Mithun Sreedharan: @Todd No performance issue with current query, i just want make it "shorter" in terms of text length
• davidsleeps: maybe because you are not using it? it's definition is for posting code for peer review.
• Andriy M: Maybe your question will inspire some people to visit the site slightly more often, and some others to come up with further interesting questions. :)
• Malachi: What is the purpose of the Query? What does the rest of the query look like?

Answer 1

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
)

Answer 2

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

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

Answer 3

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

Answer 4

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 ) )

Way cleaner already.

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.