March 18, 2010 at 8:10 am
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
March 18, 2010 at 8:20 am
March 18, 2010 at 8:46 am
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
March 18, 2010 at 8:50 am
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
March 18, 2010 at 9:05 am
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
March 22, 2010 at 7:17 pm
Check this: http://www.sqlservercentral.com/articles/T-SQL/63003/
Also you can pass it as XML parameter, parse and insert it.
Thanks,
Nikul
March 23, 2010 at 8:31 am
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply