Begin-Commit Transaction

  • 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)

    Sachin Thamke
    Mumbai, India
  • Use in stored procedure only. Once you complete your transaction commit it.

  • 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

  • i'd say control the transactions from the app. you may want to consider using xml to send your header and details in an xml string and then do your stuff in the stored proc.


    Everything you can imagine is real.

  • 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.

    Sachin Thamke
    Mumbai, India
  • 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"

  • [font="Verdana"]

    sachinthamke (6/18/2008)


    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.

    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

  • 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