|
|
Posted by Hugo Kornelis on 04/28/06 01:17
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
Navigation:
[Reply to this message]
|