sql server - Get the value of a stored procedure inside another stored procedure with select

Question

I have this stored procedure as you can see

create PROCEDURE [dbo].[SPViewMTOByLineIdAndTestPackageId] 
    @PackId int
AS
BEGIN
    SELECT      
        *, 
        ISNULL(dbo.ReturnShortageByItemCodeLinePackage(LineId, TestPackageId, MaterialDescriptionId), 0) AS Shortage,
        ISNULL(dbo.ReturnTotalIMIVByLineIdAndTestPackIdAndMaterialDescriptionId(LineId, TestPackageId, MaterialDescriptionId), 0) AS totalIMIV,
        ISNULL(dbo.ReturnTotalMIVByLineIdAndTestPackIdAndMaterialDescriptionId(LineId, TestPackageId, MaterialDescriptionId), 0) AS TotalMIV,
       ISNULL(dbo.ReturnTotalMRCByLineIdAndTestPackIdAndMaterialDesriptionId(LineId, TestPackageId, MaterialDescriptionId), 0) AS TotalMRC,
       ISNULL(dbo.WarehouseByMaterialdesciptionId(MaterialDescriptionId), 0) AS Warehouse
FROM
    dbo.ViewMTO 
WHERE 
    TestPackageId = @PackId

As you can see, this stored procedure accepts an input and return some values. I want to call this stored procedure from another stored procedure with select statement to get the values like this :

CREATE PROCEDURE secondSP
AS
    declare @a nvarchar(max)
BEGIN
    select @a = shortage 
    from SPViewMTO(1)    // The input value @PackId is 1 for example
END

Answer 1

create a table value function for getting packet info:

CREATE FUNCTION dbo.SPViewMTOByLineIdAndTestPackageId(@PackId int)
    RETURNS @packInfo TABLE 
    (
        Shortage int , 
        totalIMIV int , 
        TotalMIV int , 
        JobTitle int , 
        TotalMRC int 
    )
    AS 
    -- Returns the first name, last name, job title, and contact type for the specified contact.
    BEGIN
    SELECT      *,isnull(dbo.ReturnShortageByItemCodeLinePackage(LineId,TestPackageId,MaterialDescriptionId),0) As Shortage
    ,isnull(dbo.ReturnTotalIMIVByLineIdAndTestPackIdAndMaterialDescriptionId(LineId,TestPackageId,MaterialDescriptionId),0) as totalIMIV
    ,isnull(dbo.ReturnTotalMIVByLineIdAndTestPackIdAndMaterialDescriptionId(LineId,TestPackageId,MaterialDescriptionId),0) as TotalMIV
    ,isnull(dbo.ReturnTotalMRCByLineIdAndTestPackIdAndMaterialDesriptionId(LineId,TestPackageId,MaterialDescriptionId),0) as TotalMRC
               ,isnull(dbo.WarehouseByMaterialdesciptionId(MaterialDescriptionId),0) As Warehouse

     from 
    dbo.ViewMTO where  TestPackageId=@PackId
    END

Modify your second proc as below :

   Create PROCEDURE secondSP
    AS
    declare @a nvarchar(max)
    BEGIN

            select @a=shortage from   dbo.SPViewMTOByLineIdAndTestPackageId(1)

    END