where clause

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an arbitrary number of values in the WHERE clause:

select foo
from bar
where
foobar = '1'
and foobar = '2'
...
and foobar = 'n-1'
and foobar = 'n'

What's the best way to program this?

Thank you.
 
Dynamically create sql statement.
And use parameters for value passing.
 
Addition to Miha's suggestion.

Use "IN (...)" in Where clause would be easier for when using dynamic SQL.
That is,

SELECT ... FROM...WHERE foobar IN ('1', '2', '3',....n-1,n-2).

BTW, in your sample, if you really meant "...WHERE foobar='1' AND fobbar='2'
AND ...." (notice the ANDs), then you may not get anything selected: it not
possible for a record's column value to be equal to more that one values.
You probably means "OR", not "AND".
 
So, I want to use parameter passing to fill in the values that I'm going to
place in my 'in' clause, because I'd prefer to declare my SelectCommand
parameter in my constructor, and then execute it in the other code bits

so, like
myAdapter.SelectCommand = new SelectCommand("select foo from bar where
hoopajoob in (@something)");
myAdapter.SelectCommand.Parameters.Add("@something", stuff, stuff, huh?);

1. How do I declare that a parameter is really a set of values, not a single
value? 2. and then how do I pass such a value into my parameter at code
execution time?

Right now I'm building my selectcommand in main code by using a
StringBuilder object, but that seems vulnerable and kludgy to me compared
with parameter-passing and declaration in the constructor.

Thanks for your help.
 
dynamic sql

Ohako said:
So, I want to use parameter passing to fill in the values that I'm going
to
place in my 'in' clause, because I'd prefer to declare my SelectCommand
parameter in my constructor, and then execute it in the other code bits

so, like
myAdapter.SelectCommand = new SelectCommand("select foo from bar where
hoopajoob in (@something)");
myAdapter.SelectCommand.Parameters.Add("@something", stuff, stuff, huh?);

1. How do I declare that a parameter is really a set of values, not a
single
value? 2. and then how do I pass such a value into my parameter at code
execution time?

Right now I'm building my selectcommand in main code by using a
StringBuilder object, but that seems vulnerable and kludgy to me compared
with parameter-passing and declaration in the constructor.

Thanks for your help.
Click to expand...
 
As some have indicated, you can't pass a string via parameter and expect
TSQL to compile it. That's because by the time the parameter is applied, the
query has already been compiled.
There are a number of approaches that work, however. First, you can create a
TableValue function that accepts a string (your delimited list of items) and
returns an in-memory table. This table can be use in the IN clause

.... WHERE X IN (SELECT item FROM myTVF(@MyDSParameter)

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
While dynamic SQL is a technique that works, it opens your system to SQL
injection attacks. It's not recommended.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Good solution!

William (Bill) Vaughn said:
As some have indicated, you can't pass a string via parameter and expect
TSQL to compile it. That's because by the time the parameter is applied,
the query has already been compiled.
There are a number of approaches that work, however. First, you can create
a TableValue function that accepts a string (your delimited list of items)
and returns an in-memory table. This table can be use in the IN clause

.... WHERE X IN (SELECT item FROM myTVF(@MyDSParameter)

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________
Click to expand...
 
Thank you for your lively replies.

Norman,

my problem is twofold.

[1]
BTW, in your sample, if you really meant "...WHERE foobar='1' AND fobbar='2'
AND ...." (notice the ANDs), then you may not get anything selected: it not
possible for a record's column value to be equal to more that one values.
Click to expand...

That's precisely what I'm experiencing. But I need and want AND, not OR. I
must retrieve only foobars that meet all the criteria. How do I define my
query?

[2]
Then the programming. I may have to do it by dynamic string concatination.
 
Think about it. WHERE Color=Red AND Color=Yellow will find nothing. A column
can't have 2 values.

If you want foobars that meet all the criteria, then use OR

Give me all colors that are red or yellow

and not..

Give me all colors that are red and yellow (??)

bill tie said:
Thank you for your lively replies.

Norman,

my problem is twofold.

[1]
BTW, in your sample, if you really meant "...WHERE foobar='1' AND
fobbar='2'
AND ...." (notice the ANDs), then you may not get anything selected: it
not
possible for a record's column value to be equal to more that one values.
Click to expand...

That's precisely what I'm experiencing. But I need and want AND, not OR.
I
must retrieve only foobars that meet all the criteria. How do I define my
query?

[2]
Then the programming. I may have to do it by dynamic string
concatination.
Click to expand...
 
Back
Top