>> which is most efficient. <<

>> Surely this is a common problem. Are there any more elegant/efficient methods that anyone can suggest? Can one pass an array to a proc? Is this a place for a user defined data type? <<

JRoughgarden wrote:
> After a customer decides to buy a shopping list, there is generally a
> need to store/insert one master record and a variable number of child
> detail records, preferably all wrapped in a transaction. There are lots
> of ways to do this and I am wondering if anyone knows which is most
> efficient.
>
> One approach is to use ADO.NET's transaction capabilities, define
> single-record insert procs for the master and detail tables, and call
> the detail insert in a loop from the web page. This has N+1 trips to
> the server, which is not too attractive.
>
> Another approach is to concatenate all the data into a big
> string/varchar variable and pass it to a decoder proc that would then
> call the single record insert procs via a loop inside the decoder proc.
> This second approach would use T-SQL's transaction capability and have
> only one trip to the server, but it is more effort to code on the web
> page and in the decoder proc.
>
> Surely this is a common problem. Are there any more elegant/efficient
> methods that anyone can suggest? Can one pass an array to a proc? Is
> this a place for a user defined data type?
>
> Any advice is much appreciated.