5
\$\begingroup\$

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 ) )
\$\endgroup\$
5
  • 3
    \$\begingroup\$ 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? \$\endgroup\$
    – Todd
    Jun 1 '11 at 6:13
  • \$\begingroup\$ @Todd No performance issue with current query, i just want make it "shorter" in terms of text length \$\endgroup\$ Jun 1 '11 at 6:15
  • \$\begingroup\$ maybe because you are not using it? it's definition is for posting code for peer review. \$\endgroup\$ Jun 1 '11 at 6:28
  • \$\begingroup\$ Maybe your question will inspire some people to visit the site slightly more often, and some others to come up with further interesting questions. :) \$\endgroup\$
    – Andriy M
    Jun 1 '11 at 7:37
  • 1
    \$\begingroup\$ What is the purpose of the Query? What does the rest of the query look like? \$\endgroup\$
    – Malachi
    Jul 21 '14 at 15:03
7
\$\begingroup\$

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
)
\$\endgroup\$
7
\$\begingroup\$

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

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

\$\endgroup\$
2
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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