Geek Answers Handbook

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

I see that there is already a very similar question that discusses the same problem, but I just can not understand. Below is a copy of my code stored in a stored procedure. I need to somehow force the second insert statement to insert the CompanyID generated from the first into this second expression. I appreciate any help I can get.

Thanks!

@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)
+4
source share
5 answers

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 the MSDN documentation on SCOPE_IDENTITY() :

Returns the last identity value inserted into the identifier column to the same extent. Volume is a module: stored procedure, trigger, function, or part. Therefore, two operators are in the same area if they are in the same stored procedure, function, or batch.

+9
source

Yes. Assuming you're using SQL Server ... just use the SCOPE_IDENTITY () function to get the last inserted identifier value. In other words, replace @CompanyID with SCOPE_IDENTITY ().

+4
source 
 SET @MyFirstID = (SELECT SCOPE_IDENTITY())

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

The 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())
0
source

Find the identifier of the created record after the first identifier. This is done differently in different database systems.

For Microsoft SQL Server:

 set @CompanyId = scope_identity()
0
source

Yes. you just need to add an intermediate operator that collects the identifier, stores it in a variable, and then uses it in the second expression. So you need to add something like

 SELECT IDENT_CURRENT('dbo.CompanyTable')
-1
source

More articles:


All Articles