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

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

Solution 2

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

Comments

[arturo r]

will it do the insert of idPerson, name, phone and idProduct in table1 and idProduct, productName and productDescription in table 2?

[Admin]

Beware that SCOPE_IDENTITY() can't be relied on to return accurate results unless you use MAX_DOP=1 on SQL versions before 2012. (See here: connect.microsoft.com/SQLServer/feedback/details/328811)

[MPaul]

@@SCOPE_IDENTITY isn't a thing. Use SCOPE_IDENTITY()