Conditional/Dynamic Where Clause

Hi all,
I want to construct a dynamic where clause depending on the
value of the parameter of the stored procedure.
Here' s a snippet of the Stored procedure that I want to write
Create Procedure mySP
@.FilterBy --declare parameter
As
Select * from Registration
where
--This is where i need help.
The values for @.FilterBy can be only either A or B or C or D.
If value of @.FilterBy is A then I would like the where clause to be :
Where Registration.A = 'Something'
If value of @.FilterBy is B then I would like the where clause to be :
Where Registration.B = 'Something'
If value of @.FilterBy is C then I would like the where clause to be :
Where Registration.C = 'Something'
If value of @.FilterBy is D then I would like the where clause to be :
Where Registration.D = 'Something'
Is this possible? If yes, how? I will greatly appreciate any help.
TIA,
Mounil.
Give this a try. It should be what you are looking for I think.
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
)
AS
SET NOCOUNT ON
DECLARE @.SqlDynvarchar(4000)
SELECT @.SqlDyn = 'SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.A = "Something"'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B = "Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C = "Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D = "Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
AndyP,
Sr. Database Administrator,
MCDBA 2003 &
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"Mounilk" wrote:

> Hi all,
> I want to construct a dynamic where clause depending on the
> value of the parameter of the stored procedure.
> Here' s a snippet of the Stored procedure that I want to write
> Create Procedure mySP
> @.FilterBy --declare parameter
> As
> Select * from Registration
> where
> --This is where i need help.
>
> The values for @.FilterBy can be only either A or B or C or D.
> If value of @.FilterBy is A then I would like the where clause to be :
> Where Registration.A = 'Something'
> If value of @.FilterBy is B then I would like the where clause to be :
> Where Registration.B = 'Something'
> If value of @.FilterBy is C then I would like the where clause to be :
> Where Registration.C = 'Something'
> If value of @.FilterBy is D then I would like the where clause to be :
> Where Registration.D = 'Something'
> Is this possible? If yes, how? I will greatly appreciate any help.
> TIA,
> Mounil.
>
|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn =
'Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn?
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50)))'+ --Using
SP's Parameter in @.SqlDyn?
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B =
"Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C =
"Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D =
"Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.
|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn =
'Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn?
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50)))'+ --Using
SP's Parameter in @.SqlDyn?
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B =
"Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C =
"Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D =
"Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.

Conditional/Dynamic Where Clause

Hi all,
I want to construct a dynamic where clause depending on the
value of the parameter of the stored procedure.
Here' s a snippet of the Stored procedure that I want to write
Create Procedure mySP
@.FilterBy --declare parameter
As
Select * from Registration
where
--This is where i need help.
The values for @.FilterBy can be only either A or B or C or D.
If value of @.FilterBy is A then I would like the where clause to be :
Where Registration.A = 'Something'
If value of @.FilterBy is B then I would like the where clause to be :
Where Registration.B = 'Something'
If value of @.FilterBy is C then I would like the where clause to be :
Where Registration.C = 'Something'
If value of @.FilterBy is D then I would like the where clause to be :
Where Registration.D = 'Something'
Is this possible? If yes, how? I will greatly appreciate any help.
TIA,
Mounil.Give this a try. It should be what you are looking for I think.
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn = 'SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.A = "Something"'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B = "Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C = "Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D = "Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
AndyP,
Sr. Database Administrator,
MCDBA 2003 &
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"Mounilk" wrote:
> Hi all,
> I want to construct a dynamic where clause depending on the
> value of the parameter of the stored procedure.
> Here' s a snippet of the Stored procedure that I want to write
> Create Procedure mySP
> @.FilterBy --declare parameter
> As
> Select * from Registration
> where
> --This is where i need help.
>
> The values for @.FilterBy can be only either A or B or C or D.
> If value of @.FilterBy is A then I would like the where clause to be :
> Where Registration.A = 'Something'
> If value of @.FilterBy is B then I would like the where clause to be :
> Where Registration.B = 'Something'
> If value of @.FilterBy is C then I would like the where clause to be :
> Where Registration.C = 'Something'
> If value of @.FilterBy is D then I would like the where clause to be :
> Where Registration.D = 'Something'
> Is this possible? If yes, how? I will greatly appreciate any help.
> TIA,
> Mounil.
>|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn ='Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn'
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50)))'+ --Using
SP's Parameter in @.SqlDyn'
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B ="Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C ="Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D ="Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn ='Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn'
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50)))'+ --Using
SP's Parameter in @.SqlDyn'
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B ="Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C ="Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D ="Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.

Conditional/Dynamic Where Clause

Hi all,
I want to construct a dynamic where clause depending on the
value of the parameter of the stored procedure.
Here' s a snippet of the Stored procedure that I want to write
Create Procedure mySP
@.FilterBy --declare parameter
As
Select * from Registration
where
--This is where i need help.
The values for @.FilterBy can be only either A or B or C or D.
If value of @.FilterBy is A then I would like the where clause to be :
Where Registration.A = 'Something'
If value of @.FilterBy is B then I would like the where clause to be :
Where Registration.B = 'Something'
If value of @.FilterBy is C then I would like the where clause to be :
Where Registration.C = 'Something'
If value of @.FilterBy is D then I would like the where clause to be :
Where Registration.D = 'Something'
Is this possible? If yes, how? I will greatly appreciate any help.
TIA,
Mounil.Give this a try. It should be what you are looking for I think.
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn = 'SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.A = "Something"'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B = "Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C = "Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D = "Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
AndyP,
Sr. Database Administrator,
MCDBA 2003 &
Sybase Certified Pro DBA (AA115, SD115, AA12, AP12)
"Mounilk" wrote:

> Hi all,
> I want to construct a dynamic where clause depending on the
> value of the parameter of the stored procedure.
> Here' s a snippet of the Stored procedure that I want to write
> Create Procedure mySP
> @.FilterBy --declare parameter
> As
> Select * from Registration
> where
> --This is where i need help.
>
> The values for @.FilterBy can be only either A or B or C or D.
> If value of @.FilterBy is A then I would like the where clause to be :
> Where Registration.A = 'Something'
> If value of @.FilterBy is B then I would like the where clause to be :
> Where Registration.B = 'Something'
> If value of @.FilterBy is C then I would like the where clause to be :
> Where Registration.C = 'Something'
> If value of @.FilterBy is D then I would like the where clause to be :
> Where Registration.D = 'Something'
> Is this possible? If yes, how? I will greatly appreciate any help.
> TIA,
> Mounil.
>|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn =
'Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn'
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50))
)'+ --Using
SP's Parameter in @.SqlDyn'
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B =
"Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C =
"Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D =
"Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.|||Hi Andy,
Firstly, thanks a lot for your reply; it is greatly
appreciated. Sorry, but I have another problem with the dynamic sql.
I'll try and explain this. If I am not clear, please let me know and
i'll give it another try.
My question is :- Can I use a parameter (that i declare for the stored
procedure) inside the Dynamic Sql ie (@.SqlDyn) ? for example,
CREATE PROCEDURE MySP
(
@.FilterBy char(1) = 'A'
@.DateRange varchar(30)
)
AS
SET NOCOUNT ON
DECLARE @.SqlDyn varchar(4000)
SELECT @.SqlDyn =
'Declare @.DateFrom varchar(10)
Declare @.DateUntil varchar(10)
Set @.DateFrom = substring(@.DateRange,1,10) --Using SP's Parameter in
@.SqlDyn'
Set @.DateUntil = ltrim(rtrim(substring(@.DateRange,12,50))
)'+ --Using
SP's Parameter in @.SqlDyn'
' SET QUOTED_IDENTIFIER OFF ' +
'SELECT * FROM registration WHERE '
IF (@.FilterBy = 'A')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.Date between
convert(datetime,@.DateFrom) and convert(datetime,@.DateUntil)'
END
IF (@.FilterBy = 'B')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.B =
"Something"'
END
IF (@.FilterBy = 'C')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.C =
"Something"'
END
IF (@.FilterBy = 'D')
BEGIN
SELECT @.SqlDyn = @.SqlDyn + ' Registration.D =
"Something"'
END
EXEC (@.SqlDyn)
SET NOCOUNT OFF
I tried doing this but i get an error in Query Analyzer( when i try to
execute the SP) that I need to declare @.DateRange. How do I accomplish
this?
TIA,
Mounil.sqlsql

Conditional Where using a Parameter

How do I construct a select with a conditional where:

If DefaultWH is not blank I want to add a "AND part".

SELECT DISTINCT Name
FROM Warehouse
WHERE (Cono = @.Company)

CASE WHEN

@.DefaultWH' <> ' ' THEN

AND (@.DefaultWH = whseid)

END

Here is something that I have done. Rather than use a blank, set it to null and use an isnull.

Example:

If @.DefaultWh = ''
Begin
Set @.DefaultWh = NULL
End

Select Distinct Name
from Warehouse
Where (Cono = @.Company)
and isnull(@.DefaultWH, WhseID) = WhseID

Hope that helps!

BobP

|||

Try :

SELECT DISTINCT Name
FROM Warehouse
WHERE ((Cono = @.Company) and (@.DefaultWH='')) or ((Cono = @.Company) and (@.DefaultWH=whseid))

Hope it work's...

|||

Neith of the examples worked. I need to have the query run in 1 of 2 formats

Parameter 1 = select company

Parameter 2 = select DefaultWH

Parameter 3 =

SELECT DISTINCT Name
FROM Warehouse
WHERE (Cono = @.Company) < === from Parameter 1

or

SELECT DISTINCT Name
FROM Warehouse
WHERE (Cono = @.Company) AND (@.DefaultWH = whseid) <===From Parameter 1 and 2

I need to be able to drop or include the "AND part" based on the value of Parameter 2

Parameter 1 runs and I pick a company#

Parameter 2 runs and it returns DefafultWH as a blank (access to all warehouses) or a value (limited to that warehouse) based on the Company# enter for Parameter 1.

If Parameter 2 is blank then run Parameter 3 -- Select without the AND

If Parameter 2 is not blank then run Parameter 3 -- Select with the AND part

It just cant be that hard to to. I just have not been that this that long to figure it out.

|||

My example would work in both scenarios.

This is what it does:

The ISNULL syntax returns the value of @.DefaultWH if it is NOT null, and the value of WhseID if it is. So, If parameter2 is blank, change it to null, and the AND clause will look like this:

Parameter1 = 'ABC Company'

Parameter2 = blank (Unselected)

Select Distinct Name
from Warehouse
Where Cono = 'ABC Company'
and WhseID = WhseID

So this effectively removes the AND. The only caveat to this: If WhseID can be NULL, then you would need to add one more statement.

Select Distinct Name
From Warehouse
Where Cono = @.Company
And (WhseID = isnull(@.DefaultWH,WhseID) or WhseID is null)

You have to add the "WhseID is null" clause because null does not "=" null, it only IS null.

Feel free to email me at bobp1339
at
yahoo

BobP

|||

Where does the code go

If I insert on the generic query screeen is says that @.DefaultWH is not declared or defined

If I insert the following into the generic code screen a

=Code.GetSQL()

and then insert the code in the Report code section I get errors there to.

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

Conditional Query

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,

-- JeffJeff Mason (je.mason@.comcast.net) writes:
> 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.

I have an article on by web site that discusses a couple of alternatives,
both with static and dynamic SQL:
http://www.sommarskog.se/dyn-search.html

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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