Adding Auto-Increment ID in both parent and child table

[@myg@l]

Hie,

I tried out samples in MSDN regarding how to enable autoincrement of an ID through Stored Procedure, it works. Now I'm having doubts where I wanted to insert the ID which is auto-incremented in parent table when a new record is added, to be inserted in the child table.
I just make an illustration for example, StaffAccount Table (Parent Table)having StaffID as primary key, it is auto-incremented using stored procedures-succesfully completed, now the StaffID is the foreign key in Login Table(Child Table), I want the StaffID to be added automaticallly in child table once a new record has been inserted in parent table.

How is this possible?


Thanks in advance for helping in reading this.
=)

best regards amygal

 

[TechGnome]

Use @@Identity to get the new ID (look it up in SQL Books Online for more details on it.)

Then use it when inserting into the child table.

-tg

 

[@myg@l]

Hi tg,
Thanks for reply
however
but it seemed having some prob:
I had tried the stored procedure u had specify, but is generate error says that no null value can be inserted into the Login Table. Alright, I had set the StaffID in child table of Login AS Not Allow Null, I guess tis is the prob, but yet if i changed the setting to Set as Allow Null, the result the same- IT does not insert the auto-generated StaffID in (Staff Record)Parent Record to the (Login)child table.
Here is my Procedure

Create PROCEDURE InsertStaffAccount
@StaffName varchar(20),
@Gender char(10),
@Address varchar(50),
@Position varchar(20),
@@Identity int OUT

AS
SET nocount On
INSERT INTO StaffAccount(StaffName,Gender,Address,Position) VALUES (@StaffName,@Gender,@Address,@Position)
INSERT INTO StaffLogin(StaffID,Password) VALUES (@@Identity,@@Identity)
go

any Idea??

Thanks in advance

best regards
amygal

 

[TechGnome]

Yeah.... as I said... look up on the use of @@IDENTITY...

Create PROCEDURE InsertStaffAccount
@StaffName varchar(20),
@Gender char(10),
@Address varchar(50),
@Position varchar(20),
@NewID int OUT

AS
SET nocount On
INSERT INTO StaffAccount(StaffName,Gender,Address,Position) VALUES (@StaffName,@Gender,@Address,@Position)
SET @newID = @@IDENTITY
INSERT INTO StaffLogin(StaffID,Password) VALUES (@NewId,??????)
go

I added the ????? because where the heck does the password come from?

-tg