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 &lt;&gt; 'Products')
        OR (@Max IS NOT NULL AND @Type = 'Products'
            AND ProductCount &gt;  @Max ) )

    AND ( (@Min IS NULL OR @Type &lt;&gt; 'Products')
        OR (@Min IS NOT NULL AND @Type = 'Products'
            AND ProductCount &lt;  @Min ) )

    AND ( (@Max IS NULL OR @Type &lt;&gt; 'Vendors')
        OR (@Max IS NOT NULL AND @Type = 'Vendors'
            AND VendorCount &gt;  @Max ) )

    AND ( (@Min IS NULL OR @Type &lt;&gt; 'Vendors' )
        OR (@Min IS NOT NULL AND @Type = 'Vendors'
            AND VendorCount &lt;  @Min ) )

    AND ( (@Max IS NULL OR @Type &lt;&gt; 'Order')
        OR (@Max IS NOT NULL AND @Type = 'Order'
            AND OrderCount &gt;  @Max ) )

    AND ( (@Min IS NULL OR @Type &lt;&gt; 'Order')
        OR (@Min IS NOT NULL AND @Type = 'Order'
            AND OrderCount &lt;  @Min ) )
You will have to test this carefully, but the following query should work:

WHERE 
( 
  @Max IS NULL 
  OR @Type = 'Products' AND ProductCount &gt; @Max
  OR @Type = 'Vendors'  AND VendorCount  &gt; @Max
  OR @Type = 'Order'    AND OrderCount   &gt; @Max
)
AND
(
  @Min IS NULL
  OR @Type = 'Products' AND ProductCount &lt; @Min
  OR @Type = 'Vendors'  AND VendorCount  &lt; @Min
  OR @Type = 'Order'    AND OrderCount   &lt; @Min
)