Passing a SP a Variable with multiple values. 1

[Gatorajc]

I had previously posted a question (thread333-831424) regarding about how I would query Tires,Wheels,Caps. If I should break it up or was there something in SQL. Got a great answer

WHERE YourCriteria IN(YourCommaDelimitedListOfSelectedItems)

Well the problem I am now having is How do I get that variable into the Stored Procedure.

Ive tried different ways

CommandProducts = "Tires,Wheels,Caps"
CommandProducts = "'Tires','Wheels','Caps'"
CommandProducts = "'Tires,Wheels,Caps'"

all throw different errors. Any Ideas how I would pass it to the Proc as is. I could parse it and loop but i was hoping to avoid that.

Thanks in advance.
Albert

 

[Veep]

Your SQL is going to need to look something like:
SELECT whatever FROM yourTable WHERE yourCriteria IN('Tires','Wheels','Caps') providing they are all strings.

So...you need to perform some string manipulation on the comma separated list that is posted over.

<%
myVar = Request.Form("WhateverYouCallIt")
myArray=split(myVar,",")
myInClause = "("
for i = 0 to UBound(myArray)
	myInClause = myInClause & "'" & myArray(i) & "',"
Next
theLen = Len(myInClause)
myInClause = left(myInClause,theLen-1)
myInClause = myInClause & ")"

'check to see what it looks like
'response.Write("<br>" & myInClause)

'add it to the SQL statement

strSql="SELECT * FROM myTable WHERE myCriteria IN" & myInClause
%>

 

[Veep]

I guess that I should have read the other thread first.

 

[Gatorajc]

Just in case anyone has the same probelem.

I got my answer probabaly should have posted this in the SQL Server Programming section. I just passed in the string as a varchar.

set @String = '''' + Replace('TIRES_CAPS_WHEELS','_',''',''') + ''''

Then put that in my query string.

AND OrderProduct IN(' + @String + ')

Worked great.

AJ


Do you feel lucky?