June 18, 2008 at 5:53 am
Hi
I have two procedures one is master insert, detail insert.
So in this where i m suppose to user the Begin-Commit Transaction in front end or backend.
Currently i m calling these procedures separately from front end (vb.net)
June 18, 2008 at 6:29 am
Use in stored procedure only. Once you complete your transaction commit it.
June 18, 2008 at 7:38 am
Different people do it different ways. You can control the transaction from the application, but you need to be very cautious about getting extraneous code in the way of the transaction, holding locks & blocks open longer on the database than you need to. You can also control the transaction through the stored procedures, but in this case, I'd recommend you create a wrapper proc that calls each of the other two in the correct order within a transaction.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 18, 2008 at 2:47 pm
June 18, 2008 at 11:01 pm
Thanks for u r replay
I m using the stored procedure for storing usp_InsertMaster,usp_InsertDetail. the detail sp used to store the items for sending the XML parameters there is a limit of 8000 char.
What's why i can not create the wrapper procedure e.g.
June 18, 2008 at 11:53 pm
Hi
I have'nt understood your point... but if 8000 char is a limitation you can use xml data type or Varchar(MAX) .
"Keep Trying"
June 19, 2008 at 2:29 am
[font="Verdana"]
sachinthamke (6/18/2008)
Thanks for u r replayI m using the stored procedure for storing usp_InsertMaster,usp_InsertDetail. the detail sp used to store the items for sending the XML parameters there is a limit of 8000 char.
What's why i can not create the wrapper procedure e.g.
Sachin, it seems that you are inserting data in Parent and Child table as well. then why don't you merge the two Insert statements into single Sproc so that you can easily control the transaction. And you don't need to make one more call the DB as well. In a single call you can do all the stuff.
Mahesh[/font]
MH-09-AM-8694
June 19, 2008 at 5:30 am
Use identity variable to keep the relation intact, 🙂
happy programming!!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply