Bill wrote:
But I am still stumped about the WHERE clause. It seemed like the sp
could only accept single this=that or t1.f1 = @param. I need to use an
IN condition and the number of fields will change with user selection.
For example my WHERE clause will be something like;
WHERE t1.f1 IN ('a','c','d') and t2.f1 IN ('j','m','z') one time
and WHERE t2.f1 IN ('j','m','y') and t2.f1 IN ('123','1233') another.
Tim:
rudy's suggestions were the right way to go. Here's a couple of simple
examples that might help you out further. I've modified them from some
of my existing code so they may not work perfectly the first time
around, but they're close enough to give you the right idea.
I assume you're getting your list of values from a multiselect option
box on the webpage. If so, you need to combine the selections into a
single string. I do this in JavaScript that runs when the page is
submitted:
var i, cmb, submittext;
// grab select box data
cmb = document.all.cmbSELECT;
submittext = "";
for (i=0; i<=cmb.length-1; i++) {
if (cmb.options[i].selected) {
if (submittext.length > 0) submittext = submittext + ",";
submittext = submittext + "'"cmb.options[i].value.toString(10)
+ "'";
}
}
document.all.HiddenField.value = submittext;
If you're getting your values some other way, you can use the same
method (JS on page submit) to assemble it into a string. Now I've got
the 'a','c','d' part into a single string, contained in a hidden HTML
field, that will submit with the page. The page then submits to the
processing ASP page, which grabs the hidden value, calls the SP and
sends in the string as one of the parameters. In the SP you'd do
something like this:
declare @strSQL as varchar(1000)
set @strSQL = 'select [list of fieldnames] from [tablename] '
set @strSQL = @strSQL + 'where t1.f1 in (' + @ValuesPassedIn + ')'
exec (@strSQL)
You can send in text strings for the WHERE part, too, if you have
different needs at different times (i.e "WHERE t1.f1 IN..." and "WHERE
t2.f2 IN...").
Make sure your @strSQL variable is large enough to handle the largest
incoming set of values. As was stated before, the SP probably won't
compile (or compile completely), but will still give you a centralized
way of processing data. I've got several multi-function SPs that have
an integer passed in along with the parameters; the integer specifies
which function to perform. The functions are all related, similar to
what overloading is in .NET. (no, I haven't learned .NET yet, I'm still
reading about it :P ).
HTH.
Tim
___________________________
Tim Furry
Web Developer
Foulston Siefkin LLP
____ • The WDVL Discussion List from WDVL.COM • ____
To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED]
Send Your Posts To: [EMAIL PROTECTED]
To change subscription settings to the wdvltalk digest version:
http://wdvl.internet.com/WDVL/Forum/#sub
________________ http://www.wdvl.com _______________________
You are currently subscribed to wdvltalk as: archive@jab.org
To unsubscribe send a blank email to [EMAIL PROTECTED]
