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
id int not null primary key
idPerson int foreign key
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 .