Transaction in VS2008 and SQL Server 2005 Stored Procedure

Question

If I'm already using transactions in my stored procedure, is it feasible to use transactions in VS 2008 to call the procedure multiple times?

What I am trying to do is re-use my insert single record stored procedure which already uaes transactions. I want to insert multiple records using this insert single record stored procedure, and rollback if exceptions happen.

I have a feeling that this is not best practice, and I suspect it may cause problems.

Any ideas?

Answer 1

It is possible, but most of the time would be round-trips to the database server.

Suggestions:

• load temp table, call stored proc on same connection
• use XML/Table parameter on stored proc
• Batch the stored proc calls (which means submitting a big string)