split a string in sql

[m_commons]

Hi I need a stored procedure in SQL that will split a comma separated variable passed to it
select a name for each value and return a recordset. Any pointers greatfully received.
First attempt is dreadfully slow as I am opening recordsets each time

Function func_getFood()
Dim rsfoodsql
Dim foodoutput
for x=1 to ubound(masterfoodarray)-2
set rsfoodsql= objconn.execute ("select foodname from tbl"&language&"food where foodID='"& masterfoodarray(x) &"'")
if not rsfoodsql.eof then
foodoutput=rsfoodsql("foodname")
if not foodoutput="" then
response.write foodoutput&"<BR>"
end if
end if
next
End Function

Hope someone can help,
cheers

[andi_g69]

You do not necessarily need a SP to speed up your function. The first bottleneck is in your VB code. You fire one query per foodID. This is causing a lot of connection overhead. If you change your function like this:

Function func_getFood()
Dim rsfoodsql
Dim foodoutput
Dim sfoodcommand
Dim sfoodname

sfoodcommand = "select foodname from tbl"&language&"food where foodID IN ("

for x=1 to ubound(masterfoodarray)-2
sfoodcommand = sfoodcommand & "'masterfoodarray(x) &"',"
Next

sfoodcommand = Left (sfoodcommand, Len (sfoodcommand)-1) & ")"
set rsfoodsql= objconn.execute (sfoodcommand)
foodoutput = ""
While not rsfoodsql.eof
sfoodname = rsfoodsql("foodname")
if not sfoodname="" then
foodoutput = foodoutput & sfoodname&"<BR>"
end if
rsfoodsql.MoveNext
wend
rsfoodsql.Close

response.write foodoutput

End Function

Actually this contains two improvements:
1) Only one query for all criteria is fired against the DB.
2) The Response.Write method is executed only once.

[mist]

Read this article on CSV lists

http://www.sqlteam.com/item.asp?ItemID=11499

Which should help when you change your code to a procedure.

Regardless of which method you choose, procedure or multiple argument ad-hoc sql code, I'd recommend using the getrows() ASP command instead of movenext and line by line. Getrows is much more efficient and means you can close the connection earlier, then do what you wish with your output.