I need some help with a stored procedure for insert.
I know this might sound stupid but I'm a beginner and I know the answer is there but I just can't find it.

I've searched the site but I couldn't find an answer to clear me out ...or maybe i didn't know how or what to search .
So here's my problem:
I have 2 tables :
id int primary key not null
First name nvarchar
Second name nvarchar

Phone Numbers
id int not null primary key
idPerson int foreign key
Phone Number

I need to create a stored procedure to Insert all 3 values at once from an asp.net page where there are 3 textboxes

If I do :

create procedure InsertNumbers
Insert into Persons (FirstName, SecondName) values (@FirstName, @SecondName)
Insert into PhoneNumbers (idPerson,PhoneNumber) values ((Select id from Persons where id=idPerson),@PhoneNumber)

it says in this context subqueries are not allowed (and I'm pretty sure it is not corect..something is wrong in that procedure)

the final form of the procedure is this one:


ALTER PROCEDURE [dbo].[sp_InsertAll]
@FirstName nvarchar(50),
@SecondName nvarchar(50),
@PhoneNumber nvarchar(50))
Insert into PhoneNumbers(idPerson)

select id from persons 

 Insert into phoneNumbers (PhoneNumber) values (@PhoneNumber)
    Insert into persons (FirstName,SecondName) values (@FirstName,@SecondName)


what is does is ... it inserts 2 records at once in the phoneNumbers table: one record with id and idperson and another record with id, null at idperson and the PhoneNumber I've inserted in the textbox ...that's weird:)
I know it's something I don't do but I just can't imagine what .
Thank you all for any help .

INSERT INTO PhoneNumbers (idPerson)
    SELECT id FROM Persons

Will insert any/all ID's from Persons into Phonenumbers.

INSERT INTO PhoneNumbers (PhoneNumber) VALUES (@PhoneNumber)

This one then adds a new record with just the phonenumber.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.