Tell me more ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.
up vote 2 down vote favorite

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 ) )
share|improve this question
3  
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 – Mithun P Jun 1 '11 at 6:15
maybe because you are not using it? it's definition is for posting code for peer review. – davidsleeps 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. :) – Andriy M Jun 1 '11 at 7:37

migrated from stackoverflow.com Jun 1 '11 at 17:34

3 Answers

up vote 5 down vote accepted

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
)
share|improve this answer
up vote 6 down vote

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
share|improve this answer
2  
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
1  
@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
up vote 2 down vote

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.