How to write a stored procedure to insert values in multiple tables

Question

Database Schema

How do I write a stored procedure to add a person record with multiple addresses?

It is easy if the person has only one address but I'm not sure how to write a stored procedure to add a person with multiple addresses. Here is the stored procedure to add a person with one address:

 DELIMITER $$

 CREATE PROCEDURE `log`.`spAddPerson` (
 IN personID INT,
 IN personName VARCHAR(100),
 IN addressLine1 VARCHAR(45),
 IN addressLine2 VARCHAR(45),
 IN myCity VARCHAR(45),
 IN myState VARCHAR(45),
 IN myCountry VARCHAR(45)

)

 BEGIN
 DECLARE EXIT HANDLER FOR SQLEXCEPTION 
 BEGIN
      ROLLBACK;
 END;

 START TRANSACTION;

 INSERT INTO person VALUES(personID,personName);
 -- addressid is automatically generated
 INSERT INTO address(Line1, Line2,City,State,Country) VALUES
 (addressLine1, addressLine2, myCity,myState, myCountry);

 INSERT INTO personAddress(personID, last_insert_id());

 COMMIT;

 END

The above code works fine. However, I do not know how to handle a person with multiple addresses without writing a separate stored procedure. Is there a simple way to do this?

If I do what you say: Rowland, I would have to check to ensure that the person I am adding is not already in the database for each address the person has; furthermore if one of the inserts fail, I won't be able to rollback the entire procedure. — unleashed
– unleashed, Commented Aug 5, 2013 at 19:34
I think you have to split the logic of your program: First: Check if the person exists; if it doesn't exist, insert it. Second: Add the address (or addresses). — Barranka
– Barranka, Commented Aug 5, 2013 at 19:53
Barranka: I have already thought about that; however, I have three concerns: 1. How will I rollback the transaction 2. What impact will it have on performance later on 3. Is splitting the logic the best practise in this situation — unleashed
– unleashed, Commented Aug 5, 2013 at 20:29
Unrelated: is there really an many-to-many relationship between person and address, or shouldn't there just be a foreign key in address to person (one-to-many relationship). — RandomSeed
– RandomSeed, Commented Aug 6, 2013 at 0:01

RandomSeed · Accepted Answer · 2013-08-06 00:00:03Z

You cannot pass a variable number of variables to a procedure, nor a non-scalar type.

A possible trick would be building a temporary table with the addresses before calling this procedure. Either the temporary table is pre-determined, or pass its name as a VARCHAR parameter(and use it to build dynamic SQL statements). Eg:

CREATE PROCEDURE spAddPerson (tmp_table VARCHAR(10), ...)
BEGIN
    ...
    PREPARE s AS CONCAT(
        'INSERT INTO address (line1, ...) VALUES SELECT * FROM ', tmp_table
    );
    EXECUTE s;
    ...
END


-- use it like this
CREATE TEMPORARY TABLE tmp_addresses (line1 VARCHAR(255), ...);
INSERT INTO tmp_addresses VALUES ('1 Cherry Lane'), ... ;
CALL spAddPerson ('tmp_addresses', ...);

However, I would rather split the action in two parts. Do you really want to prevent the creation of the person altogether if its address creation fails? And even then, wouldn't you want to advise your user why the transaction failed (user creation or address creation)?

I would rather treat these two exceptions separately at the application level:

    issue a "START TRANSATION"
    try to insert a person (call stored proc 1)
    if it failed, rollback and notify user
    for each address
        try to insert an address (call stored proc 2)
        if it failed, rollback and notify user
    issue a "COMMIT"

Pankaj katiyar · Accepted Answer · 2015-03-20 06:54:56Z

0

>     DECLARE @LAST_INSERT_ID INT
>     DECLARE @EXECUTION_OK char(1)
>     SET @EXECUTION_OK = 1
>     
>     insert into base_table(imgPath,store,apparelType) values (imgPath,store,apparelType)
>     
>         SELECT @LAST_INSERT_ID = SCOPE_IDENTITY()
>         
>         insert into data_table(cvID,color) values (@LAST_INSERT_ID, color)
>         GO
>         
>         If exists( Select cvID from data_table where cvID= @LAST_INSERT_ID)
>         Begin
>         @EXECUTION_OK = 0
>         End

answered Mar 20, 2015 at 6:54

Pankaj katiyar

46410 silver badges26 bronze badges

Collectives™ on Stack Overflow

How to write a stored procedure to insert values in multiple tables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related