Multiple Dynamic Insert Statements in one stored procedure?

Hello, I'm having a little trouble, and need a little direction. I

have a stored procedure with a transaction that creates a contract for

a customer in a Contract table. After the contract record is created, I need

to create the contract items/products with their properties (price,

notes, etc), which belong in a linked table, so that they're created

all within the same transaction (the contract, and the products), so

that if either the contract or any of the items cannot be inserted into

their correct tables, it will rollback and undo the creation of the

contract. Now, I understand transactions and how they work, but my

trouble is that I'm not sure if I can use a dynamic insert

statement to create the contract items/products, within the same

transaction where the contract is created.

I'm new to sp_executesql, which seems to be the recommended method for dynamic sql in a stored proc, but if I understand it correctly, I'd have to call a stored proc for each item i wanted to insert something into the ContractItem table with a dynamic insert statement. Is there any way to have multiple dynamically created insert statements in one stored procedure? Help! I hope I explained my problem well enough. thanks in advance!

You can execute sp_executesql as many times as you like in a stored procedure. Simply begin a transaction at the start of the proc then either rollback or commit the transaction when appropriate.

Incidentally, is there a particular reason that you are choosing to use dynamic SQL over defining stored procedures that parameters can be passed into?

Chris

|||

Chris Howarth wrote:

You can execute sp_executesql as many times as you like in a stored procedure. Simply begin a transaction at the start of the proc then either rollback or commit the transaction when appropriate.

I understand this, I guess my problem really is this: the number of rows I will have to insert into the ContractItem table will vary each time this stored procedure to create a Contract and it's ContractItems is called. So how do I pass that (variable amount of) data into the stored prodedure to create the dynamic insert statements?

Chris Howarth wrote:

Incidentally, is there a particular reason that you are choosing to use dynamic SQL over defining stored procedures that parameters can be passed into?

Not really, I'm still sort of new to stored procedures though, if that is any kind of an excuse :) If this would be a better way to do it, then my plan should probably change slightly...

|||

How you choose to do this very much depends on your application's architecture.

If you have a web server or application server that is executing your stored procedures then it would probably make sense to create a single INSERT stored proc (designed to insert one row at a time) per table and then get your web/app server to execute the stored procedure once per row, passing in the parameters as required. Any iteration that needs to take place can then be performed by the web/app server.

You can start a transaction at the connection level, so as long as your web/app server uses the same connection to execute all stored procs that are part of the 'business transaction' then all of the changes will be rolled back should an error be raised.

Does this fit your scenario?

Chris

|||Chris,

This does fit my scenario... a transaction at the connection level sound just like what I need to get this to work properly. Now I just have to go dig up how to do that. Thank you so much for your help!