使用select获取另一个存储过程中的存储过程的值 [英] Get the value of a stored procedure inside another stored procedure with select

问题描述

如您所见，我有此存储过程

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

如您所见，此存储过程接受输入并返回一些值.我想使用select statement从另一个存储过程中调用此存储过程，以获取如下所示的值:

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

推荐答案

创建用于获取数据包信息的表值函数:

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

这篇关于使用select获取另一个存储过程中的存储过程的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！