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

Hanks

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

Lianaliane

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

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

is this possible if my other stored proc is in another database – Diseased

I would use IDENT_CURRENT('TableName') to get the most recent Id entered into a specific table. – Gramnegative

Answer 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

Jamshid

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) – Cardioid

connect.microsoft.com no-longer exists, but the issue described in the comment above is documented here: support.microsoft.com/en-us/topic/… - apparently this is fixed in SQL Server 2008 R2 SP1 and later (so you don't need to upgrade to 2012 or later just for this). – Sauterne

Answer 3

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

Ovary