Calling stored procedure from another stored procedure SQL Server

42

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 tagged with sql sql-server sql-server-2005 stored-procedures

~ Asked on 2012-01-13 23:26:24

The Best Answer is


37

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

~ Answered on 2012-01-13 23:30:19


8

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

~ Answered on 2012-01-13 23:36:20


Most Viewed Questions: