passing array parameter to a StoredProc

Question

Post reply

passing array parameter to a StoredProc

Dominic Gagné

SSCrazy

Points: 2654
More actions
March 18, 2010 at 8:10 am

#139508

Hi,
I'd like to pass an array as a parameter to a StoredProc, and within the SP, browse through every item in the array to process them. How can I do that?
thanks a lot for your time and help

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

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

By an "array", do you mean a list of items or a true multidimensional array?

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Dominic Gagné SSCrazy Points: 2654 More actions · Answer 2

I mean an single dimension array, like a list of strings. I'd like to do that because I don't want to call the same SP 25 times just to validate 25 items from a listview on my VB.net application. Juste send the 25 items once, and that's it!

thanks

GSquared SSC Guru Points: 260824 More actions · Answer 3

There are a couple of ways to do that in SQL 2005. The easiest is send a list of delimited values, and use a string-parsing routine to split it up. Do a search for "Tally table" on this site. Jeff Moden posted an article a long while back that outlines a very efficient way to do that. That one also works in SQL 2000.

Another would be to pass them in as an XML parameter, and parse that using XQuery. Works, but it's less efficient. Deals better with multiple dimensions or nested values, etc. Can't do that in SQL 2000, or at least can't do it easily.

In SQL 2008, you can pass a dataset into a table-valued parameter. If you're using 2005 (as per the forum this is posted in), you don't have that option. I mention it only because I often see questions posted in the wrong forum for the actual version of SQL being used.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Dominic Gagné SSCrazy Points: 2654 More actions · Answer 4

thanks a lot for the reply

I've looked at the article you mention. That's very interesting. I'll certainly read through it.

I'm using all version of SQL Server, from 200-2008. But in this case, I use 2008 Express. Unfortunatly, I haven't yet study what's new in 2008, so I never heard you can pass a dataset directly to a Sp ??! seems really nice!

anyway, thanks a lot again

Nik Desai SSC Enthusiast Points: 135 More actions · Answer 5

Check this: http://www.sqlservercentral.com/articles/T-SQL/63003/

Also you can pass it as XML parameter, parse and insert it.

Thanks,

Nikul

Paul White SSC Guru Points: 150467 More actions · Answer 6

Can't believe that no-one has posted a link to Erland's article on this topic yet.

Arrays and Lists in SQL Server[/url]

Best guide there is, IMO.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi