Hello!
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 :
Persons
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
(@FirstName,
@SecondName,
@PhoneNumber)
as
begin
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:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_InsertAll]
(
@FirstName nvarchar(50),
@SecondName nvarchar(50),
@PhoneNumber nvarchar(50))
AS
BEGIN
Insert into PhoneNumbers(idPerson)
select id from persons
Insert into phoneNumbers (PhoneNumber) values (@PhoneNumber)
Insert into persons (FirstName,SecondName) values (@FirstName,@SecondName)
END
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 .
