Re: Conditional Query

On Thu, 27 Apr 2006 11:10:01 -0400, Jeff Mason wrote:

(snip)
For example, I want to define a parameter named @ExcludeSpecialties which if it has
the value 1, means to return all stores but exclude stores whose StoreNumber is in
the list (800, 802, 804). If the parameter has the value 0, then it means "don't
care" and all StoreNumbers should be returned.

Hi Jeff,

WHERE ( @ExcludeSpecialties = 0 OR StoreNumber NOT IN (800, 802, 804) )

(snip)
Indeed, there is a Franchise bit column in the row which is selected by another
parameter called @ExcludeFranchise whose WHERE predicate could be written as:

WHERE Franchise = CASE WHEN @ExcludeFranchise = 1 THEN 0 ELSE Franchise END

and if all the parameters were like this, I wouldn't be posting. Sadly, for the
Specialties test I'm stuck with a NOT IN list.

That is indeed a common method to write such queries.

Do read the article Erland posted a link to - it describes a bunch of
methods to achieve what you need, with all their strengths and
weaknesses. Good stuff!

--
Hugo Kornelis, SQL Server MVP
.