CODEWITHSUNDEEP
c#sqlperformancestored-proceduresprocessing-efficiency
Adnand
Adnand

Reputation: 572

Should we use a single execution stored procedure with table parameters or multiple execution procedure with separated parameters?

I am working on a WinForms Desktop Application. On this application I have to make some updates on SQL Server database. I can do it this with a stored procedure with table parameters 

comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.AddWithValue("@changes", changes.ToTable(false, "ItemNo", "Info"));

and execute the procedure just once, or with a loop execute the procedure multiple times with string parameters.

for (...)
{
   ...
   comm.CommandType = CommandType.StoredProcedure;
   comm.Parameters.AddWithValue("@Item", "Item 1"));`
   comm.Parameters.AddWithValue("@Info", "Info 1"));`
   int i = comm.ExecuteNonQuery();
   ...
}

Which it's the best practice to do this?

Upvotes: 0

Views: 37

Answers (1)

Rahul
Rahul

Reputation: 77876

I would go with the first approach and send all the data as a table type variable even if you are performing a simple UPDATE operation.

Cause in that case, you can perform a update-join by performing a JOIN with the table type variable and can update multiple records at once. At least, you will save multiple calls to the DB.

Upvotes: 1

Related Questions