Can i update mulitple rows in a single stored proc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Can i update mulitple rows in a single stored proc

Author

Topic

Aiby
Yak Posting Veteran

71 Posts

Posted - 2004-04-06 : 04:48:26

Can i update mulitple rows of one table in a single stored proc

Suppose i have two tables

Sales SalesChild
----- ----------
InvoiceNumber InvoiceNumber
--- ProductCode
-- Qty
--- ---
NetAmount --

In a sigle transaction i may have One Record for Sales and more
than One for Child table. Can i update all in a single StoredProcedure.
My question is sending a parameters for the multiple rows of child table.. in a sigle stretch

Aiby Mohan Das
System Analyst
Decibel Infotech P.Ltd.
Kerala, India
Email: Aiby@hotmail.com

MatrixOne
Starting Member

11 Posts

Posted - 2004-04-06 : 06:38:38

Not only can you update multiple tables in a single proc but by the sounds of it, you should enclose the updates within a Transaction.

If you require a procedure to do batch inserts, it is good practice to use BEGIN TRANSACTION and COMMIT TRANSACTION to encapsulate your updates, this will ensure data integrity as a transaction will commit all or nothing.

So in your example, all rows in the child table will not be commited to the DB unless the Parent is added and vice versa.

See books online for details on how to use Transactions, look up

BEGIN TRANSACTION
COMMIT TRANSACTION
ROLLBACK TRANSACTION.

I useually run something as follows:-

BEGIN TRANSACTION

Update Parent Table
Update Child Table

IF @@Error > 0
BEGIN
ROLLBACK TRANSACTION
RAISEERROR "INSERT Failed"
RETURN
END

COMMIT TRANSACTION

Hope this helps

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-06 : 18:59:13

My question is sending a parameters for the multiple rows of child table.. in a sigle stretch

Can you tell us what you mean by this. Give us an example, because I think Aiby might be misunderstanding.

If you are wanting to pass this to the stored procedure:

Sales
InvoiceNumber Date
------------- -----
0110 01/01/01

Sales Child
InvoiceNumber ProductCode Qty
------------- ----------- ---
0110 1 5
0110 5 15

Are you wanting to insert both the record for Sales and both records for Sales Child in the same query???

Are you wanting to insert one row into child and a row into Sale if one doesn't exist already?

Can you explain a little better?

You really want to send the multiple rows to the child table, and the row to the Sale table at the same time, you will need to send an XML string to the procedure and use that to drive your insert statements.

Node 1 would be the sale. The children would the the SalesChild.

Anyone know of a better way out there to do it in one pass?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Aiby
Yak Posting Veteran

71 Posts

Posted - 2004-04-11 : 06:52:06

quote:
Originally posted by derrickleggett

My question is sending a parameters for the multiple rows of child table.. in a sigle stretch

Can you tell us what you mean by this. Give us an example, because I think Aiby might be misunderstanding.

If you are wanting to pass this to the stored procedure:

Sales
InvoiceNumber Date
------------- -----
0110 01/01/01

Sales Child
InvoiceNumber ProductCode Qty
------------- ----------- ---
0110 1 5
0110 5 15

Are you wanting to insert both the record for Sales and both records for Sales Child in the same query???

Are you wanting to insert one row into child and a row into Sale if one doesn't exist already?

Can you explain a little better?

You really want to send the multiple rows to the child table, and the row to the Sale table at the same time, you will need to send an XML string to the procedure and use that to drive your insert statements.

Node 1 would be the sale. The children would the the SalesChild.

Anyone know of a better way out there to do it in one pass?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Yes i meaned that MeanOld,
I would like to post complete Sales transaction to all the related talbes in a sigle stretch..as you understood. Cud you write a sample stored proc and demondstarte how cud i send my child data (array) to the procedure ( No of child table rows various )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources