#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    7
    Rep Power
    0

    Writing multiple insert statements into different tables in a single stored procedure


    Hi,
    As part of a project i am working on i need to insert records into 3 tables and have to write a stored procedure for that. An e.g to that affect is as follows:
    Say i have 3 tables NAMES (lname,fname), ADDRESS(addr1,addr2), PHONE(hphone). How do i write a single stored proedure that takes five arguments (lname,fname,addr1,addr2,hphone) and insert these into each of the tables mentioned above. I want to do these in the context of one transaction. I.e. if in the worse case scenario i loose connection to the database after inserting into NAMES, ADDRESS tables, i should rollback the transaction because as the hphone has not been inserted into the PHONE table.
    I would greatly appreciate any input on this issue.
    Thanks.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    No es importante
    Posts
    2,065
    Rep Power
    14
    begin transaction
    insert into table1 (...,...) values (...,...)
    if @@error <> 0
    rollback trans
    else
    insert into table2 (...,...) values (...,...)
    if @@error <> 0
    rollback trans
    else
    insert into table3 (...,...) values (...,...)
    El éxito consiste en una serie de pequeñas victorias día a día

    MySQL, MS SQL, MS ACCESS, Oracle Database Manager - http://victorpendleton.net/products/psdviewer.html

IMN logo majestic logo threadwatch logo seochat tools logo