如何从另一个过程中执行的存储过程中返回或获取输出值 [英] How to return or get output value from stored procedure which is executing inside another proceed

问题描述

我具有以下存储过程:

ALTER PROCEDURE [dbo].[spRptFundsTransferSchoolProfileSelect]          
(          
    @SessionID int ,      
    @DateRangeStart DateTime,       
    @DateRangeEnd DateTime       
)          
AS          
BEGIN        
 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED     


    --set @SessionID = 1175      

    SELECT  tss.school_id,           
            ts.school_code,          
            tss.id,           
            ts.school_name,           
            ta.address,           
            ta.city,           
            ta.zip,            

    FROM T_School ts           

    Execute [dbo].[spRptFundsTransferPaymentSummarySelect]     @SessionID ,      
                                                               @DateRangeStart ,       
                                                               @DateRangeEnd       
END 

和以下用于返回输出的模型:

and following model for returning output :

namespace FundTr.de
{
    public class FundTrans : ModelBase
    {
        public string school_name { get; set; }

        public string school_code { get; set; }

        public int school_id { get; set; }

        public long id { get; set; }

        public string address { get; set; }

        public string city { get; set; }

        public string zip { get; set; }

        public string Date_Added { get; set; }//from dbo.spRptFundsTransferPaymentSummarySelect procedure

    }
}

contolller:

contolller:

public ActionResult GetFundTransferReportTemplate(DateTime DateRangeStart, DateTime DateRangeEnd)
        {
            return SmartJson(ReportService.GetFundTransferReportTemplate(DateRangeStart, DateRangeEnd));

        }

服务:

 public static FundTransferReportTemplate GetFundTransferReportTemplate(DateTime DateRangeStart, DateTime DateRangeEnd)
        {
            long SessionID = 1175;// SmartState.session.schoolSession.SessionId;
            var repo = GetRepository<FundTransferReportTemplate>();
            var da = repo.GetSingleObject("spRptFundsTransferSchoolProfileSelect", new { DateRangeStart, DateRangeEnd, SessionID });
            return da;

        }

当我按下控制器时，我得到除最后一个"Date_Added"(过程dbo.spRptFundsTransferPaymentSummarySelect的此字段)以外的所有值.我的意思是说我没有获得dbo.spRptFundsTransferPaymentSummarySelect的输出值.我如何获得访问在主程序内部执行的程序的输出值?

when i hit the controller i get all value except last one "Date_Added"(this field from procedure dbo.spRptFundsTransferPaymentSummarySelect). i mean to say that i am not getting output value for dbo.spRptFundsTransferPaymentSummarySelect.how can i get access output value of the procedure which is executing inside main procedure?

推荐答案

SP从上至下执行，从某种意义上说，它将执行 SELECT 语句，然后执行SP spRptFundsTransferPaymentSummarySelect .假定SP spRptFundsTransferPaymentSummarySelect 返回的单个值是添加日期的

The SP executes the top to bottom, in the sense, it will execute the SELECT statement and then execute the SP spRptFundsTransferPaymentSummarySelect. Assuming that the SP spRptFundsTransferPaymentSummarySelect returns a single value which is the date_added

您需要先调用 spRptFundsTransferPaymentSummarySelect ，然后将值存储在变量 @Date_Added 中，然后在 SELECT 语句中使用它.

You will need to call the spRptFundsTransferPaymentSummarySelect first and store the value in a variable @Date_Added and then use it in the SELECT statement.

ALTER PROCEDURE [dbo].[spRptFundsTransferSchoolProfileSelect]
(
    @SessionID int,
    @DateRangeStart DateTime,
    @DateRangeEnd DateTime
)
AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    DECLARE @Date_Added DateTime

    Execute @Date_Added = [dbo].[spRptFundsTransferPaymentSummarySelect] @SessionID,
                                                                @DateRangeStart,
                                                                @DateRangeEnd

    --set @SessionID = 1175

    SELECT tss.school_id,
        ts.school_code,
        ts.id,
        ts.school_name,
        ta.address,
        ta.city,
        ta.zip,
        @Date_Added AS Date_Added
    FROM T_School ts
END 

这篇关于如何从另一个过程中执行的存储过程中返回或获取输出值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！