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')