|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
|
|
Stay one step ahead of the competition. Evaluate and give feedback
on some of the hottest web development tools on the market today.
Make your opinion heard! Click
Here
|
|
#1
|
|||
|
|||
|
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
|
|||
|
|||
|
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 |
![]() |
| Viewing: Dev Shed Forums > Databases > MS SQL Development > Writing multiple insert statements into different tables in a single stored procedure |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|
|