With Procedure for a beginner so far i've come alone but i can't understand where i'm-CodePudding

How do I solve it? the idea is to insert the records of the table people, phone and address at once, but each one with its data and respecting the ID created by the identity in the table people replicated in the others in the field people_id

Create Procedure newRegister
(
    @PeopleID int,
    @Name nvarchar(100),
    @Phone numeric(9),
    @Adress nvarchar(150),
    @NumLocal numeric(5),
    @Type_Fone nvarchar(20),
    @CodeArea numeric (3)
)

AS 

begin
INSERT INTO people values (@Name, @Phone,@Adress)
INSERT INTO adress values (@Adress,@PeopleID)
INSERT INTO phones values (@Phone,@CodeArea,@Type_Fone,@PeopleID)
end

on Exec:

exec newRegister @PeopleID = 1, @Name = "Victor", @Phone = 111222333, @Adress = 'Northen Street 55', @NumLocal = 155, @Type_Fone = 'Mobile', @CodeArea = 100

After execute, only the person table receives the data, the address table has a foreign key violation error, and the phone table also has the same error. and data is not entered.

My table people

CREATE TABLE people (
    id int IDENTITY(1,1) NOT NULL,
    name nvarchar(150) NOT NULL,
    adress nvarchar(150) NOT NULL,
    phone numeric(9) NOT NULL,
    PRIMARY KEY (id)
);

my table phone:

CREATE TABLE phone (
    id int IDENTITY(1,1) NOT NULL,
    phone numeric(9) NOT NULL,
    codearea numeric(3) NOT NULL,
    type_fone nvarchar(20) NOT NULL,
    people_id int NOT NULL
    PRIMARY KEY (id),
     CONSTRAINT FK_phone_people FOREIGN KEY (people_id)
    REFERENCES people(id)
);

my table adress:

CREATE TABLE adress (
    id int IDENTITY(1,1) NOT NULL,
    adress nvarchar(150) NOT NULL,
    numlocal numeric(5) NOT NULL,
    people_id int NOT NULL,
    PRIMARY KEY (id),
     CONSTRAINT FK_adress FOREIGN KEY (people_id)
    REFERENCES people(id)
);

CodePudding user response：

You have to specify the fields

FROM

begin
INSERT INTO people values (@Name, @Phone,@Adress)
INSERT INTO adress values (@Adress,@PeopleID)
INSERT INTO phones values (@Phone,@CodeArea,@Type_Fone,@PeopleID)
end

--Example, btw address, not adress if You are going for English
INSERT INTO PEOPLE(name, phone, adress)
VALUES(@Name, @Phone,@Adress)