arrays in store procedures?

Question

arrays in store procedures?

SQL_Programmer

SSC Veteran

Points: 248
More actions
August 5, 2005 at 9:49 am

#67463

array = (001,002,003,004,005)
I want to pass the array value to the query in the store procedure
eg :
while count <>0
select Id,Name from itemtable where Id = array(count)
count = count +1
END

thanks..

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

the only way i can see to do this is to load the array into a temp table, cursor or table variable and then loop through table/cursor

MVDBA

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

What do you want to do in the loop that you can't do in a normal update/insert statement??

BTW you might be interested in reading this.

Arrays and Lists in SQL Server

SQL_Programmer SSC Veteran Points: 248 More actions · Answer 3

Thanks man,i used the temp table to insert the parameters but i still have one issue..

In store procedure i have query like this

Select * from Item where A.RU IN (SELECT [RUIn] FROM [#ABN_RUInput]) OR A.CRU IN (SELECT [RUIn] FROM [#ABN_RUInput])

If I pass only one value like RU = 220056 then my query will be

Select * from Item where RU = 220056 or CRU = 220056

And it works fine but if I am passing multiple RU then it fails eg. [220056,220060,220065]

My query should give ---

Item where RU IN (220056,220060,220065] OR CRU IN [220056,220060,220065]

But its not giving like that, it gives all between values for CRU instead of just three CRU in above case.

Hope i was able to explain. Please advice.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Can you post the tables definition, some sample data, the exact sp code along with the expected results vs the desired ones, I can't help you without that?

Lowell SSC Guru Points: 323518 More actions · Answer 5

there's nothing wrong with using the IN operator and having only one item in the selection list:

so if [#ABN_RUInput] only has one row witht he value 220056 , it performs the same as you'd expect:

Select * from Item where A.RU IN (SELECT [RUIn] FROM [#ABN_RUInput]) OR A.CRU IN (SELECT [RUIn] FROM [#ABN_RUInput])

becomes:

Select * from Item where A.RU IN (220056 ) OR A.CRU IN (220056 )

which is functionaly the same as

Select * from Item where RU = 220056 or CRU = 220056

so don't waste any time trying to rewrite your SP to detect whether there is only one row.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!