post Master/Detail within same transaction? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

post Master/Detail within same transaction?

Author

Topic

dohamsg
Starting Member

22 Posts

Posted - 2008-10-25 : 01:39:43

Delphi2007, SDAC, SQLServer2005.

Hi everybody,

Is it possible to post data of Master/Detail within a Stored Procedure?

If Yes then HowToPlease?

Notes: I work with unbound edit Controls, I use a Stored Procedure to post data to the database, for the master no problem cause just one record; but how do I call each row of the Detail table (unbound) in order to commit finally the transaction (Master/Detail at the same time).

- I guess I should use a Transaction Component, then bound to it two Stored Procedures ( I don't create the transaction within the Stored Procedure ), then if no error post the transaction. Is this the only solution?

Thanks indeed.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-25 : 01:58:12

Seems like what you're trying to get can be achieved using OUTPUT clause available in SQL 2005. Have a look at description given in books online. As you've not posted any more info we cant suggest anything further.

http://jerrytech.blogspot.com/2008/03/how-to-use-sql-2005-output-clause.html

dohamsg
Starting Member

22 Posts

Posted - 2008-10-25 : 02:38:25

I explain more,

In my Application I Have two memory tables, one holds the Invoice data, the other one holds the InvoiceDetail data (many records).

Now I use a stored procedure to post data of the Master which outputs the MasterID, after that I loop through Detail records and post them using another stored procedure. This method uses two separate Transactions.

I'd like to know if there is a method to post the Master/Detail data in one transaction.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-25 : 02:45:41

quote:
Originally posted by dohamsg

I explain more,

In my Application I Have two memory tables, one holds the Invoice data, the other one holds the InvoiceDetail data (many records).

Now I use a stored procedure to post data of the Master which outputs the MasterID, after that I loop through Detail records and post them using another stored procedure. This method uses two separate Transactions.

I'd like to know if there is a method to post the Master/Detail data in one transaction.

you post one mster recods at a time? what all will be parameters passed for first procedure which saves a Invoice record?

dohamsg
Starting Member

22 Posts

Posted - 2008-10-25 : 03:25:24

Thank you for your answers and sorry if I can't clarify my question.

I post one Master at a time ( with begin , end Transaction ), I receive the Inserted MasterID as OUTPUT, then I use another stored procedure to post the Detail data (row by row) passing it the MasterID ( in another begin, end Transaction).

This means that the Master Transaction could be successful and the Detail Transaction could fail at any row.

My question is : as a general rule, how to post a master/detail transaction?

Thanks indeed for your patience.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-25 : 03:36:10

quote:
Originally posted by dohamsg

Thank you for your answers and sorry if I can't clarify my question.

I post one Master at a time ( with begin , end Transaction ), I receive the Inserted MasterID as OUTPUT, then I use another stored procedure to post the Detail data (row by row) passing it the MasterID ( in another begin, end Transaction).

This means that the Master Transaction could be successful and the Detail Transaction could fail at any row.

My question is : as a general rule, how to post a master/detail transaction?

Thanks indeed for your patience.

in that case you dont want two procedure you just want a single procedure. it will have both master & detail info passed as parameters. something like

CREATE PROC InvoiceDetailSave
@InvoiceNo int,
.... other fields
AS
DECLARE @Invoice_ID int
BEGIN TRAN
INSERT INTO Invoice
VALUES(@InvoiceNo,...)

SET @Invoice_ID = SCOPE_IDENTITY()
INSERT INTO InvoiceDetails (InvoiceID,..)
VALUES(@Invoice_ID,...)

COMMIT TRANSACTION
GO

dohamsg
Starting Member

22 Posts

Posted - 2008-10-25 : 04:46:50

Thanks.

That way you post only one row of the Detail table, what about the remaining rows ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-25 : 04:52:09

quote:
Originally posted by dohamsg

Thanks.

That way you post only one row of the Detail table, what about the remaining rows ?

so you will be passing value for more than 1 deatil row? can you illustrate how?

dohamsg
Starting Member

22 Posts

Posted - 2008-10-25 : 05:59:50

Yes that's my question.

How can I post Master with it's Detail rows ( many ) in one transaction?

Since I'm a beginner with SQL, I wonder how programmers do to save an invoice ( master ) with it's Lines ( Detail ), using SQL.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-25 : 07:21:20

quote:
Originally posted by dohamsg

Yes that's my question.

How can I post Master with it's Detail rows ( many ) in one transaction?

Since I'm a beginner with SQL, I wonder how programmers do to save an invoice ( master ) with it's Lines ( Detail ), using SQL.

what you need to do is pass the detail lines one by one and save. And if you really want to save them all in one stretch one method is to pass them as an xml from application and then extract and save data from front end in your procedure.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-25 : 16:18:04

http://weblogs.sqlteam.com/peterl/archive/2007/10/03/New-OUTPUT-operator.aspx

E 12°55'05.63"
N 56°04'39.26"

hityag
Starting Member

1 Post

Posted - 2008-12-31 : 01:30:48

Author Topic
dohamsg
Starting Member

10 Posts
Posted - 10/25/2008 : 01:39:43
--------------------------------------------------------------------------------

Delphi2007, SDAC, SQLServer2005.

Hi everybody,

Is it possible to post data of Master/Detail within a Stored Procedure?

If Yes then HowToPlease?

Notes: I work with unbound edit Controls, I use a Stored Procedure to post data to the database, for the master no problem cause just one record; but how do I call each row of the Detail table (unbound) in order to commit finally the transaction (Master/Detail at the same time).

- I guess I should use a Transaction Component, then bound to it two Stored Procedures ( I don't create the transaction within the Stored Procedure ), then if no error post the transaction. Is this the only solution?

Thanks indeed.

visakh16
Flowing Fount of Yak Knowledge

India
14606 Posts
Posted - 10/25/2008 : 01:58:12
--------------------------------------------------------------------------------

Seems like what you're trying to get can be achieved using OUTPUT clause available in SQL 2005. Have a look at description given in books online. As you've not posted any more info we cant suggest anything further.

http://jerrytech.blogspot.com/2008/03/how-to-use-sql-2005-output-clause.html

dohamsg
Starting Member

10 Posts
Posted - 10/25/2008 : 02:38:25
--------------------------------------------------------------------------------

I explain more,

In my Application I Have two memory tables, one holds the Invoice data, the other one holds the InvoiceDetail data (many records).

Now I use a stored procedure to post data of the Master which outputs the MasterID, after that I loop through Detail records and post them using another stored procedure. This method uses two separate Transactions.

I'd like to know if there is a method to post the Master/Detail data in one transaction.

visakh16
Flowing Fount of Yak Knowledge

India
14606 Posts
Posted - 10/25/2008 : 02:45:41
--------------------------------------------------------------------------------

quote:
--------------------------------------------------------------------------------
Originally posted by dohamsg

I explain more,

In my Application I Have two memory tables, one holds the Invoice data, the other one holds the InvoiceDetail data (many records).

Now I use a stored procedure to post data of the Master which outputs the MasterID, after that I loop through Detail records and post them using another stored procedure. This method uses two separate Transactions.

I'd like to know if there is a method to post the Master/Detail data in one transaction.

--------------------------------------------------------------------------------

you post one mster recods at a time? what all will be parameters passed for first procedure which saves a Invoice record?

dohamsg
Starting Member

10 Posts
Posted - 10/25/2008 : 03:25:24
--------------------------------------------------------------------------------

Thank you for your answers and sorry if I can't clarify my question.

I post one Master at a time ( with begin , end Transaction ), I receive the Inserted MasterID as OUTPUT, then I use another stored procedure to post the Detail data (row by row) passing it the MasterID ( in another begin, end Transaction).

This means that the Master Transaction could be successful and the Detail Transaction could fail at any row.

My question is : as a general rule, how to post a master/detail transaction?

Thanks indeed for your patience.

visakh16
Flowing Fount of Yak Knowledge

India
14606 Posts
Posted - 10/25/2008 : 03:36:10
--------------------------------------------------------------------------------

quote:
--------------------------------------------------------------------------------
Originally posted by dohamsg

Thank you for your answers and sorry if I can't clarify my question.

I post one Master at a time ( with begin , end Transaction ), I receive the Inserted MasterID as OUTPUT, then I use another stored procedure to post the Detail data (row by row) passing it the MasterID ( in another begin, end Transaction).

This means that the Master Transaction could be successful and the Detail Transaction could fail at any row.

My question is : as a general rule, how to post a master/detail transaction?

Thanks indeed for your patience.

--------------------------------------------------------------------------------

in that case you dont want two procedure you just want a single procedure. it will have both master & detail info passed as parameters. something like

CREATE PROC InvoiceDetailSave
@InvoiceNo int,
.... other fields
AS
DECLARE @Invoice_ID int
BEGIN TRAN
INSERT INTO Invoice
VALUES(@InvoiceNo,...)

SET @Invoice_ID = SCOPE_IDENTITY()
INSERT INTO InvoiceDetails (InvoiceID,..)
VALUES(@Invoice_ID,...)

COMMIT TRANSACTION
GO

dohamsg
Starting Member

10 Posts
Posted - 10/25/2008 : 04:46:50
--------------------------------------------------------------------------------

Thanks.

That way you post only one row of the Detail table, what about the remaining rows ?

visakh16
Flowing Fount of Yak Knowledge

India
14606 Posts
Posted - 10/25/2008 : 04:52:09
--------------------------------------------------------------------------------

quote:
--------------------------------------------------------------------------------
Originally posted by dohamsg

Thanks.

That way you post only one row of the Detail table, what about the remaining rows ?

--------------------------------------------------------------------------------

so you will be passing value for more than 1 deatil row? can you illustrate how?

"@dohamsg",

Do you get the solution for the same?

@Others,

I am using ADO.NET and backend as SQL. I have almost the same problem as before.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 03:04:25

did you try any of suggestions that we have posted till now? You should be able to do thgis with help of OUTPUT operator in sql 2005

Samuel Santos
Starting Member

1 Post

Posted - 2009-08-18 : 09:25:11

Hi,

Have you found a solution for your question?
I have the exact same problem, and I managed to workaround it, but I don't really like my solution...

What I've done is create a string and concatenate all fields using a delimiter. Then, I send to the stored procedure this large string, and the stored procedure goes through it in order to determine the actual values.

Example:

sp_save_Invoice @myInvoiceString

And @myInvoiceString is something like:
'custid#invoicedate#salespersonID#deliverydate#prod1#price1#quant1#discount1#prod2#price2#quant2#discount2#prod3#price3#....'

Obviously, in fact it will be:

'701#20090817#Samuel Santos#20090821#313#140#1#0#113#200#10#0#890#9#....'

You know that the first parameters are for the master record, and the rest of the string will have 4 parameters for each detail line. Then what you have to do is parse the string.

If you found another solution, please share it! Thank you!

samuelpt@gmail.com

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-08-19 : 08:10:27

The above 'solution' is the worst, most ridiculous suggestion I have ever seen. Please do NOT ever consider this.

The original problem is not an SQL problem, it's a language one.
You need to read your Delphi manual and work out how to go
Begin transaction
insert master, returning ID
for each deail
insert detail
next
commit
As far as I can tell in your original statement you just have one commit too early and an extra begin trans.
I actually suggest you go to a Delphi forum.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources