Conditional Selection

Hi,
Hi,
I'm trying to construct a query (in a stored procedure) which will have a nu
mber of
selection criteria based on input parameters. There are a number of these p
arameters
whose selection conditions they represent which all have to be true for a ro
w to be
returned in the resultset.
The basic query is:
SELECT Store, StoreNumber
FROM Stores
WHERE ...
I'm trying to come up with the WHERE clause.
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.
One could certainly argue that there probably should have been an column in
the
Stores row to indicate the store is a specialty store, rather than using a h
ard-wired
list of numbers. But the current data schema cannot be easily changed. Bes
ides, the
list never changes.
Indeed, there is a Franchise bit column in the row which is selected by anot
her
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.
This is easy enough to do in an IF/ELSE block, but there are several such si
milar
parameters whose values may be specified in any combination. This, I think,
makes
IF/ELSE impractical as the number of IF/ELSE statements to handle all possib
le
combinations would grow very quickly.
I'm hoping there is a simple solution to this NOT IN list, and it's just tha
t I can't
see it.
Can anyone help?
Thanks,
-- JeffTry this first ( Several popular approaches are details here ):
http://www.sommarskog.se/dyn-search.html
Anith|||try this in your where clause. Let me know if this helps
((@.ExcludeSpecialties = 0) or (storenumber not in (800, 802, 804)))|||You could store the specialties flag in a seperate table, with StoreNumber
as the key, then query against it instead of using the hardcoded list. This
way, when a new specialty store opens, or one of the existing stores
changes, you will just insert a row into the table and not have to touch the
code. It would be better to have it in the original table, but if you can't
change the original, maybe adding a new table is an option...
create table SpecialtyStores
(StoreNumber integer, Specialty bit) -- add PK and FK info here
SELECT Store, StoreNumber
FROM Stores
left outer join SpecialtyStores as spec
on stores.StoreNumber = spec.StoreNumber
WHERE
Specialty = CASE WHEN @.ExcludeFranchise = 1 THEN 0 ELSE 1 END
"Jeff Mason" <je.mason@.comcast.net> wrote in message
news:vvl1525lk0m8baqqpv5vs5q1g36a9c6jpa@.
4ax.com...
> Hi,
> Hi,
> I'm trying to construct a query (in a stored procedure) which will have a
number of
> selection criteria based on input parameters. There are a number of these
parameters
> whose selection conditions they represent which all have to be true for a
row to be
> returned in the resultset.
> The basic query is:
> SELECT Store, StoreNumber
> FROM Stores
> WHERE ...
> I'm trying to come up with the WHERE clause.
> 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.
> One could certainly argue that there probably should have been an column
in the
> Stores row to indicate the store is a specialty store, rather than using a
hard-wired
> list of numbers. But the current data schema cannot be easily changed.
Besides, the
> list never changes.
> 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.
> This is easy enough to do in an IF/ELSE block, but there are several such
similar
> parameters whose values may be specified in any combination. This, I
think, makes
> IF/ELSE impractical as the number of IF/ELSE statements to handle all
possible
> combinations would grow very quickly.
> I'm hoping there is a simple solution to this NOT IN list, and it's just
that I can't
> see it.
> Can anyone help?
> Thanks,
> -- Jeff|||On Thu, 27 Apr 2006 08:13:02 -0700, Omnibuzz <Omnibuzz@.discussions.microsoft
.com>
wrote:

>try this in your where clause. Let me know if this helps
>((@.ExcludeSpecialties = 0) or (storenumber not in (800, 802, 804)))
Duh.
That did it. I knew it was something simple. I was having a Brain Fog, I gu
ess.
Thank you.
-- Jeff