CODEWITHSUNDEEP
sql-serverstored-procedures
BCartolo
BCartolo

Reputation: 719

Multiple inserts in stored procedures

I have a many to many relation between a table product and a table order, the link table is ProductInOrder. I want to make a stored procedure with a list of product ids as inputs and what it would do is create a new order and add a new record to the link table for each product.

I have now achieved this using a client initiated transaction, then calling a sp to create a new order, then a loop in the client to call a sp to create each record in the link table. But there must be a way to do this in sql.

Any ideas?

Best

Upvotes: 0

Views: 993

Answers (2)

doug_w
doug_w

Reputation: 1370

This is another option that performs wonderfully and shortens the number of round-trips to the server from your application.

http://msdn.microsoft.com/en-us/library/bb510489.aspx

Performs very well in the scenarios I have used it in. Really excellent option. There are examples out on the web that can be viewed as I am unable to locate my Proof of Concept I implemented for it. It does require the creation of a table datatype on the database you are targeting.

Upvotes: 0

Eric
Eric

Reputation: 95133

What you want is something like this (for MSSQL, at least):

declare @Sql varchar(max) = '
insert into ProductInOrder (OrderID, ProductID)
select
    ' + @OrderID + ',
    ProductID
from
    Product
where
    ProductID in (' + @Products + ')'

sp_executesql(@Sql)

Pass @Products in as a comma-delimited string of integers, e.g.-'1,2,3,15,100'.

To load up a temp table in C#, you'd do this (using a SqlCommand named comm):

comm.CommandText = "create table #ProductList (ProductID int)";
comm.ExecuteNonQuery();
comm.CommandText = "insert into #ProductList (ProductID) values (@ProductID)";
foreach(int product in products)
{
    comm.Parameters.Clear();
    comm.Parameters.AddWithValue("@ProductID", product);
    comm.ExecuteNonQuery();
}
comm.Parameters.Clear();
comm.Parameters.AddWithValue("@OrderID", orderId);
comm.CommandText = "insert into ProductInOrder (OrderID, ProductID) " +
    "select @OrderID, p.ProductID from Product p inner join #ProductList pl on " +
         "p.ProductID = pl.ProductID";
comm.ExecuteNonQuery();
comm.CommandText = "drop table #ProductList";
comm.ExecuteNonQuery();

Upvotes: 1

Related Questions