0

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 .

Edited by Dazzler123

2
Contributors
1
Reply
11
Views
4 Years
Discussion Span
Last Post by pritaeas
0
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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.