sending multiple parameters to an sp.

Question

sending multiple parameters to an sp.

Reddy-309776

Mr or Mrs. 500

Points: 503
More actions
March 30, 2006 at 11:07 am

#152872

how do i send multiple values to an sp? say i have to insert 25 rows into a table A, instead of calling the sp 25 times can't i send 25 values to it and let it insert?

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

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

Joe Salvatore SSCrazy Points: 2174 More actions · Answer 1

SQL stored procedure parameters do not support arrays of values natively.

That being said, you can pass a comma separated list into a stored procedure parameter defined as nvarchar(x) where x represents a reasonable lenght for the values you'll be passing in including the comma separators.

Then you can, in the body of the procedure, extract the individual values one at a time from the parameter and pass them into an insert statement that has been enclosed in a loop.

Like all things SQL there are many other alternatives that others may (hopefully) share with you.

Reddy-309776 Mr or Mrs. 500 Points: 503 More actions · Answer 2

how do i "extract the individual values one at a time from the parameter "?

can i get an example.

thanks

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

For an example, you could look at this post

**ASCII stupid question, get a stupid ANSI !!!**

Joe Salvatore SSCrazy Points: 2174 More actions · Answer 4

Thanks for the followup on this posting - great reference for how to do this in that other posting.

Vee Hall of Fame Points: 3028 More actions · Answer 5

the best solution implement this is to pass xml input to stored procedure. and that is the best way. take all the data grid value and construct the xml and passito stored procedures , and look for documention on how to use xml inside the stored procedures

sp_xml_removedocument

sp_xml_preparedocument

are handy....