Array in Stored Procedure

[vlitim]

Is there anything similar in Stored procedures to that of looping through an array in ASP?? ie:

stateID="1,2,3,87"
stateArray = Split(stateID,",&quot
For i=0 To UBound(stateArray)
//calcs here
Next

Cheers

Tim

 

[redgate]

Unfortunately not. You will have to use a cursor or look carefully at what you want to do. Cursors are not recommended. Where are you getting the data for your array? Post an example of what you want to achieve.

 

[vlitim]

Basically I have a form on a page with a multiple select element where the user can choose any number of countries to search on.

so when I get to the results page I have a variable

countryID = "1,3,65"

From there I need to construct annother variable looking Like:

temp = "Partners.countryID=1 OR Partners.countryID=3 OR Partners.countryID=65"

so I can then insert it into an SQL statement. At the moment I am constructing the string on an ASP page and passing it to the stored procedure which is not ideal, and I now need to be able to do it all in the stored procedure!!

Any help or ideas would be most appreciated

Thanks
Tim

 

[nikhilparchure]

What u will be passing is a comma seperated string ?

Ans : Using charindex() and substring() in SQL
seperate these values

eg : "1,3,65"

While Looping(seperating) insert into #Country
such that u'r Country table will look as
#Country
Country_id
1
3
65

Now what u can do is use the above as a subquery in the
join
temp in ( Select distinct Country_id from #Country )

Hope that helps

 

[tlbroadbent]

See thread183-483640 and faq183-3132. If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm