passing a list to stored procedure for inserting

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

passing a list to stored procedure for inserting

Author

Topic

BitShift
Yak Posting Veteran

98 Posts

Posted - 2007-12-11 : 14:14:35

Ive got a situation where I would need to insert from 1-500 records. Each record would require sql to generate a unique and random alphanumeric string. That part was answered for me in another thread, but now im wondering if it would be better to pass the list of data i need inserted as a list (xml) to the procedure, or call the procedure for each insert needed. Im thinking that passing the list would be better, performance wise. Ok, so I pass the list, then how do I loop over each item in the xml and do the insert ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 14:17:37

http://www.sommarskog.se/arrays-in-sql-2005.html

E 12°55'05.25"
N 56°04'39.16"

BitShift
Yak Posting Veteran

98 Posts

Posted - 2007-12-11 : 14:29:45

Thats almost there, he has an example of inserting several rows at once via xml. http://www.sommarskog.se/arrays-in-sql-2005.html#XML

However, I will not know how many rows I will need to insert, so I will need to loop through the xml.

BitShift
Yak Posting Veteran

98 Posts

Posted - 2007-12-11 : 14:32:48

Ahhh, this is more like it
http://discuss.fogcreek.com/dotnetquestions/default.asp?cmd=show&ixPost=4503

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 14:35:50

What?
You do not need to know beforehand how many records there are in the XML file...

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-11 : 14:50:20

[code]-- Mimic XML stored procedure parameter
DECLARE @Orders XML

SET @Orders =
'<Orders>
<ID>1000</ID>
<ID>1001</ID>
<ID>1002</ID>
<ID>1003</ID>
<ID>1004</ID>
<ID>1005</ID>
<ID>1006</ID>
<ID>1007</ID>
<ID>1008</ID>
<ID>1009</ID>
<ID>1010</ID>
<ID>1011</ID>
<ID>1012</ID>
<ID>1013</ID>
<ID>1014</ID>
<ID>1015</ID>
<ID>1016</ID>
<ID>1017</ID>
<ID>1018</ID>
<ID>1019</ID>
<ID>1020</ID>
</Orders>'

-- Show all user supplied OrderID's
SELECT O.ID.value('.', 'INT')
FROM @Orders.nodes('Orders/ID') AS O(ID)[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-18 : 09:33:55

Was this helpful or not?

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources