StoredProc: Multiple selected params 2

[Mr3Putt]

I should, perhaps, post this in the ASP forum, but I'll give it a go here first...

My current problem: I've got a form on an Intranet page with a "SELECT" object, like:

<select name=&quot;sel1&quot; id=&quot;sel1&quot; size=&quot;8&quot; multiple>
  <option value=&quot;value1&quot;>Value 1</option>
  <option value=&quot;value2&quot;>Value 2</option>
  <option value=&quot;value3&quot;>Value 3</option>
  <option value=&quot;value4&quot;>Value 4</option>
  <option value=&quot;value5&quot;>Value 5</option>
</select>

..which can have MULTIPLE items selected. There are also about 8 other parameters derived from the Intranet page...

Why that's a problem: Well, I try to pass the parameters to a Stored Procedure for processing, which will then return a recordset for display. How the dickens do I get multiple values passed to my single-parameter-name, so I can do something like:

WHERE sValue IN ('value1', 'value2', 'value3')

... where all those values were passed by the ONE SELECT OBJECT.

That's as clear as I can make it. Any suggestions will be greatly appreciated.

 

[eramgarden]

see if this helps:

http://www.databasejournal.com/features/mssql/article.php/1552701

 

[LogicalmanUS]

Mr3Putt,

How is your ASP processed for submit?
As with checkboxes, ASPs have a habit of creating comma-delimited strings of values that are all attributed to the same element ID/name.
This being the case, when the ASP refreshes (or is passed onto a new ASP), you can retrieve this string value using:

Dim myString
myString = Request("sel1&quot

The only drawback is that for a text data string to be used in an IN statement, each value has to be separated.
The string retrieved above would look similar to :
"value1,value2,value3"

To do this you can use the REPLACE function:

myString = "'" & REPLACE(myString,",","','&quot & "'"

this will produce a string of:

"'value1','value2','value3'"

Now, this value can be passed to a SProc, but, unfortunately the SProc will parse off the leading apostrophe's, (don't ask me why, I couldn't fathom it out either!). So, to use it in the SProc, you should create a SQL string of the command, and add the string to it:

DECLARE @mySQL varchar(1000)

SET @mySQL = ''
SET @mySQL = @mySQL + 'SELECT * FROM myTable '
SET @mySQL = @mySQL + 'WHERE myVal IN('
SET @mySQL = @mySQL + char(39) + myString + char(39)
SET @mySQL = @mySQL + ')'

EXEC (@mySQL)

Hope this assists,

Logicalman

 

[JamesLean]

Also this FAQ might help:

Passing a list of values to a Stored Procedure
faq183-3979

--James

 

[Mr3Putt]

Thanks James and eramgarden,

James: I had looked for FAQs on this subject and failed to find them... I guess I buggered my search criteria.

I eventually worked out the use of a Temporary Table (we're still using SQL7.0... we'll switch to 2K in about 2 months... bad timing).

LogicalmanUS, I had considered writing dynamic SQL, but it's too kludge for me... and the permissions thing is more complicated, too.

So, my searches are now returning proper results. Thanks, all, for your willing assistance.

-Mr3Putt