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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] Calling stored procedure from another stored procedure SQL Server

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to stored-procedures