Web Form/SQL Server 2005 Stored Proc

CoolBoardr

Hi,

I have a web form where users can enter in new customers – they can enter in as many new customers as they like and then hit submit.

ie.

customer #1’s name, customer #1’s email
customer #2’s name, customer #1’s email
customer #3’s name, customer #1’s email

SUBMIT!

When they hit submit I want to call a stored procedure to insert this data into a single customers table (SQL Server 2005). The stored proc currently has 2 input parameters - @name, @email.

My question – do I have to call the stored proc for each new customer/row that needs adding (ie. 3 times), or is there a way to just call it once and insert all the data at the same time.

Thanks

marco_polo

Can you alter the proc or create a new one? If so a quick and easy way I can think of would be to join all the names and emails into one delimited string and then send this to a new stored proc. You could then break up the string inside the new stored proc and call the existing one repeatedly. There are functions available in SQL Server to do this.

A sample format would be something like "name1,email1:name2,email2:name3,email2"

CoolBoardr

Yeah i am creating the stored proc myself so can do what I want - i'd just like to know the best way of designing it.

Sending it through as one long string doesn't seem ideal, it might have to do if I can't think of any other way though!

Cheers

Ginger

SQL 2005 supports XML as an input param so you can send it that way if you serialise your data.

Nice examples http://weblogs.asp.net/jgalloway/archive/2007/02/16/passing-lists-to-sql-server-2005-with-xml-parameters.aspx

Also http://www.codeproject.com/KB/database/openxml.aspx

Just do a INSERT INTO with the select command against your XML .. my head is not with it today

CoolBoardr

Ah that's a great solution. Thanks Ginger.