How to call or execute a procedure inside another stored procedure

Problem: Executing a stored procedures in one another stored procedure.

Solution: 

Nesting a stored procedure and passing the parameters from the nested procedure using output or return variables.

Each time a stored procedure is called or executed inside another stored procedure that’s called nested procedure.

Example:

First let’s create a procedure to get a company_ID from company table. I am going to call it using an outer procedure where it inserts the company_ID which is output from the inner procedure.

Please see the sample script below, company table.

CREATE PROCEDURE [dbo].[getCompanyIDbyCompanyName]

@CompanyName Varchar(50) ,

@companyID INT OUTPUT

AS

BEGIN

  SELECT @companyID = Company_Id

  FROM [dbo].[Company]

  where  [Name] =@CompanyName

  END

CREATE  PROCEDURE dbo.InsertEmployeesByCompany @Name VARCHAR(32)

                ,@phoneNumber INT = NULL

                ,@employeetype VARCHAR(32)= NULL

                ,@CompanyName VARCHAR(32)

AS

BEGIN

                DECLARE              @companyID int

EXEC      [dbo].[getCompanyIDbyCompanyName]

                                @CompanyName = @CompanyName,

                                @companyID = @companyID OUTPUT

                                INSERT INTO dbo.Employees (

                                                NAME

                                                ,PhoneNumber

                                                ,EmployeeType

                                                ,Company_Id

                                                )

                                VALUES (

                                                @Name

                                                ,@PhoneNumber

                                                ,@EmployeeType

                                                ,@companyID

                                                )

END

 -- Executing procedure to pass employee details and company name.

 EXEC     [dbo].[InsertEmployeesByCompany]

                                @Name = N'Smith',

                                @phoneNumber = 123456789,

                                @employeetype = N'developer',

                                @CompanyName = N'CompanyA'

-- Results after executing

SELECT *  from dbo.employees where name ='Smith'