I have 3 insert stored procedures each SP inserts data in 2 different tables
Table 1 Table 2
idPerson idProduct
name productName
phoneNumber productdescription
FK-idProduct
SP for table 1 SP for table 2
create procedure test1 create procedure test2
WITH WITH
EXECUTE as caller EXECUTE as caller
AS AS
declare declare
@idPerson int, @idProduct int,
@name varchar(20), @productName varchar(50),
@phone varchar(20) @productoDescription varchar(50)
SET nocount on; SET nocount on;
Begin Begin
insert into table1( insert into table2(
idPerson, idProduct,
name, productName,
phone) productDescription)
values( values(
@idPerson, @idProduct,
@name, @productName,
@phone) @productDescription)
end end
I need to call stored procedure test 2 from stored procedure test 1 and insert the FK-ID in the table 1
This question is related to
sql
sql-server
sql-server-2005
stored-procedures
Simply call test2
from test1
like:
EXEC test2 @newId, @prod, @desc;
Make sure to get @id
using SCOPE_IDENTITY(), which gets the last identity value inserted into an identity column in the same scope:
SELECT @newId = SCOPE_IDENTITY()
First of all, if table2
's idProduct is an identity, you cannot insert it explicitly until you set IDENTITY_INSERT
on that table
SET IDENTITY_INSERT table2 ON;
before the insert.
So one of two, you modify your second stored and call it with only the parameters productName
and productDescription
and then get the new ID
EXEC test2 'productName', 'productDescription'
SET @newID = SCOPE_IDENTIY()
or you already have the ID of the product and you don't need to call SCOPE_IDENTITY()
and can make the insert on table1
with that ID
You could add an OUTPUT parameter to test2, and set it to the new id straight after the INSERT using:
SELECT @NewIdOutputParam = SCOPE_IDENTITY()
Then in test1, retrieve it like so:
DECLARE @NewId INTEGER
EXECUTE test2 @NewId OUTPUT
-- Now use @NewId as needed
Source: Stackoverflow.com