Hello,

I have an employers and an address table in my database. The employers table contains an address_id field that refers to the employers address in the address table. I would like to have a stored procedure that inserts the employers address into the address table and then the address_id into the employers table with the other employer details. I'm no SQL expert and I don't have a clue how to do this. Any ideas? Thanks

This procedure assumes that employer_id and Address_Id (Primary keys) are integer identity fields in your Employers and Employers_Address table

CREATE PRocEDURE ProcAddEmployers

@Employer_Name varchar(50) = NULL,
@Employer_Address varchar(50) = NULL,
@Employer_Address2 varchar(50) = NULL,
@Employer_City varchar(30) = NULL,
@Employer_State char(2) = NULL,
@Employer_Zip varchar(10) = NULL,
@Employer_Phone varchar(14) = NULL,
@Employer_Fax varchar(14) = NULL,
@Employer_Email varchar(50) = NULL

AS

BEGIN

--Insert into Address Table
INSERT INTO Employers_Address (Address,Address2,City,State, Zip, Phone, Fax, Email)
VALUES
(@Employer_Address,@Employer_Address2,@Employer_Ci ty,@Employer_State, @Employer_Zip,@Employer_Phone,@Employer_Fax,@Emplo yer_Email)

DECLARE @Address_ID int --Foreign key
SET @Address_ID = @@IDENTITY --ID of the last inserted record

--Insert into Employers Table
INSERT INTO Employers (Employer_Name,Address_ID) VALUES (@Employer_Name,@Address_ID)

END

Go