Reduce the code in a WHERE clause without using dynamic SQL
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
)