Calling stored procedure from another stored procedure SQL Server

Question

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

Answer 1

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