Is it possible to create a stored procedure with two insert statements where id/primary_key from the first insert statement will be used in second?

I see that there is a very similiar question already answered that discusses this same issue, but i just cannot seem to understand it. Below is a copy of my code that is in a stored procedure. I need to somehow, have the second insert statement insert the CompanyID generated from the first, into this secon开发者_StackOverflow中文版d Statement. I appreciate any help i can get.

Thank You!!

@comp_name nvarchar (50),
@City nvarchar (50),
@State nvarchar (10),
@Address ntext,
@Zip_Code nvarchar (50),
@Country nvarchar (50),
@cust_name nvarchar (50),
@CompanyID int
AS
INSERT INTO Company_Listing
(comp_name, City, State, Address, Zip_Code, Country)
VALUES (@comp_name, @City, @State, @Address, @Zip_Code, @Country)

INSERT INTO Customer_Listing
(cust_name, City, State, Address, Zip_Code, Country, CompanyID)
VALUES (@comp_name,@City,@State,@Address,@Zip_Code,@Country,@CompanyID)

---

Assuming this is for SQL Server - Yes, use SCOPE_IDENTITY:

@comp_name nvarchar (50),
@City nvarchar (50),
@State nvarchar (10),
@Address ntext,
@Zip_Code nvarchar (50),
@Country nvarchar (50),
@cust_name nvarchar (50),
@CompanyID int
AS
INSERT INTO Company_Listing
(comp_name, City, State, Address, Zip_Code, Country)
VALUES (@comp_name, @City, @State, @Address, @Zip_Code, @Country)

INSERT INTO Customer_Listing
(cust_name, City, State, Address, Zip_Code, Country, CompanyId)
VALUES (@comp_name,@City,@State,@Address,@Zip_Code,@Country,SCOPE_IDENTITY())

From MSDN's documentation on SCOPE_IDENTITY():

Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.

---

Yes. Assuming you are using SQL Server ... just use the SCOPE_IDENTITY() function to retrieve the last inserted identity value. In other words, replace @CompanyID with SCOPE_IDENTITY().

---

SET @MyFirstID = (SELECT SCOPE_IDENTITY())

--now you have your ID from your first insert statement, namely @MyFirstID

Code becomes:

@comp_name nvarchar (50),
@City nvarchar (50),
@State nvarchar (10),
@Address ntext,
@Zip_Code nvarchar (50),
@Country nvarchar (50),
@cust_name nvarchar (50),
@CompanyID int
AS
INSERT INTO Company_Listing
(comp_name, City, State, Address, Zip_Code, Country)
VALUES (@comp_name, @City, @State, @Address, @Zip_Code, @Country)

INSERT INTO Customer_Listing
(cust_name, City, State, Address, Zip_Code, Country, CompanyID)
VALUES (@comp_name,@City,@State,@Address,@Zip_Code,@Country,SCOPE_IDENTITY())

---

Pick up the id of the created record after the first id. This is done differently in different database systems.

For Microsoft SQL Server:

set @CompanyId = scope_identity()

---

Yes. you just need to add an interim statement that collects the ID, store it in a variable and then use that in your second statement. So you would need to add something like

SELECT IDENT_CURRENT('dbo.CompanyTable')