Insert procedure in two tables with Foreign Key relation ship

I was wondering how I do to insert values in two tables that are related each other by a FK?

That is the procedure that illustrate what I meant to be.

ALTERProcedure [dbo].[new_user]

@.masternchar(10),

@.nicknchar(10),

@.fishnchar(10),

@.e_mailnchar(30)

As

Begin

INSERTINTO users

(nick, fish, e_mail)

VALUES (@.nick,@.fish,@.e_mail)

INSERTINTO friends

(user_id, e_mail)

VALUES (Selectuser_idfrom userswhere nick=@.master,@.e_mail)

End

Thank you very much.

ALTER Procedure [dbo].[new_user]@.masternchar(10),@.nicknchar(10),@.fishnchar(10),@.e_mailnchar(30)AsBeginSET NOCOUNT ONDeclare @.useridintINSERT INTO users (nick, fish, e_mail)VALUES (@.nick,@.fish,@.e_mail)SELECT @.userid = SCOPE_IDENTITY()INSERT INTO friends ([user_id], e_mail)VALUES (@.userid, @.e_mail)SET NOCOUNT OFFEnd

|||

Thanks to reply ndinakar.

The value inserted "@.userid" in friends table, it is not the same just inserted in the users table. This value is determined by a consult in users table, where nick = "@.master", the return of this consult will say which user_id I will insert in friends table.

|||So do you still need to do an INSERT into the users table with the values you receive in the stored proc or are the values just to be used for lookup?|||

The value @.master is for lookup the table users and gets the user_id. The values @.nick, @.fish, @.e_mail, are for create a new user. And is inserted a new record in friends table with user_id equals to the value consulted.

ALTERProcedure [dbo].[new_user]

@.masternchar(10),

@.nicknchar(10),

@.fishnchar(10),

@.e_mailnchar(30)

As

Begin

Declare@.returned_valuenchar(10)

Selectuser_idfrom userswhere nick=@.master

--I garante it will return a unique value. Supose to be called @.returned_value.

INSERTINTO users

(nick, fish, e_mail)

VALUES (@.nick,@.fish,@.e_mail)

INSERTINTO friends

(user_id, e_mail)

VALUES (@.returned_value,@.e_mail)

End

That will result a data base. Which the new user created will be related with the person who added.

|||

ALTER Procedure [dbo].[new_user]@.masternchar(10),@.nicknchar(10),@.fishnchar(10),@.e_mailnchar(30)AsBeginDeclare @.returned_valueint-- assuming userid is numeric if not I would recommend nvarchar instead of nchar.Select @.returned_value = [user_id]from userswhere nick=@.master--I garante it will return a unique value. Supose to be called @.returned_value.INSERT INTO users (nick, fish, e_mail)VALUES (@.nick,@.fish,@.e_mail)INSERT INTO friends ([user_id], e_mail)VALUES (@.returned_value,@.e_mail)End

|||Thank you very much indeed. But at last why you would recommend nvarchar instead of nchar?|||

When you use char, the length becomes a fixed size. So even if you send in a value less than the specified length, SQL pads the value with blank spaces to make it the fixed length.

So 'abc' <> 'abc '. Besides it is also a waste of space/memory. If you use varchar (Variable char) it allocates only as much is required, up to the specified length.

|||

On the select statement I mentioned that it will return a unique value, but if it return nothing or more fields?

If don't want waste your time explaining to me, could just give me the word that I look for on the internet? I tried some but I would never find about this problem specifically. And just wondering, do you know if Microsoft pays some one to stay here in this forum answering questions?

Thank you very much indeed.