在C＃中捕获嵌套存储过程的输出

I am calling one stored procedure (2) from within another stored procedure (1). Both stored procedures return a dataset in the form of a SELECT statement at the end. I need to capture both of these in my c# code, however, my code is only capturing the Select dataset of stored procedure (2). Please guide how can I accomplish this?

我从另一个存储过程(1)中调用一个存储过程(2)。两个存储过程在结尾处以SELECT语句的形式返回数据集。我需要在我的c#代码中捕获这两个,但是,我的代码只捕获存储过程的Select数据集(2)。请指导我该如何做到这一点?

Here is the format of the procedures:

以下是程序的格式:

CREATE PROCEDURE dbo.GoalStop
    @ID INT,
    @StopDate [DATETIME] = NULL
WITH EXECUTE AS CALLER
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Return varchar(500);
    BEGIN TRY
        UPDATE dbo.Table2
        SET StopDate = @StopDate 
        WHERE TableID = @ID

        SET @Return = 'The Goal was successfully stopped.';

        SELECT 'DATABASE MESSAGE: ' + @Return
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK

        DECLARE @ErrMsg VARCHAR(4000), @ErrSeverity INT

        SELECT 
            @ErrMsg = ERROR_MESSAGE(), 
            @ErrSeverity = ERROR_SEVERITY()

        RAISERROR(@ErrMsg, @ErrSeverity, 1)
    END CATCH
END

And the calling stored procedure is:

调用存储过程是:

CREATE PROCEDURE [dbo].[Goal_Restart]
    @ID [INT],
    @Goal [DECIMAL](18, 8) = NULL,
    @StartDate [DATETIME] = NULL,
    @StopDate [DATETIME] = NULL,
    @Updatedby [VARCHAR](8),
    @UpdateDate [DATETIME] = NULL
WITH EXECUTE AS CALLER
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Return varchar(500)

    BEGIN TRY

    SELECT  @Name = Name 
    FROM [dbo].Table1
    WHERE ID = @ID

    --bunch of IF ELSE Statements...

        BEGIN
            EXEC dbo.GoalStop  @ID, @StopDate --Call to other stored procedure
            INSERT INTO [dbo].Table1([Name], [Goal], [StartDate], [StopDate], [Updatedby], [UpdateDate])
            VALUES (@Name, @Goal, @StartDate, @StopDate, @Updatedby, @UpdateDate)

            SET @Return = 'The Goal was successfully created.';             
         END
     END

     SELECT 'DATABASE MESSAGE: ' + @Return

    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK

        DECLARE @ErrMsg VARCHAR(4000), @ErrSeverity INT
        SELECT @ErrMsg  =  ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY()
        RAISERROR(@ErrMsg, @ErrSeverity, 1)
    END CATCH
END

and the C# code calling this procedure:

和调用此过程的C#代码:

string returnString = string.Empty;
SqlDataReader reader;

try
{
    using (SqlConnection connection = Connection.GetSqlConnection())
    {
        using (SqlCommand command = new SqlCommand("dbo.Goal_Restart", connection))
        {
            command.CommandType = CommandType.StoredProcedure;
            command.CommandTimeout = 90;

            command.Parameters.Add("@ID", SqlDbType.Int).Value = ID;
            command.Parameters.Add("@Goal", SqlDbType.Decimal).Value = goal;
            command.Parameters.Add("@Updatedby", SqlDbType.Char, 7).Value = Updatedby;
            command.Parameters.Add("@UpdateDate", SqlDbType.DateTime).Value = updateDate;

            if (startDate != null)
            {
                command.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = startDate;
            }

            if (stopDate != null && (Convert.ToDateTime(stopDate)).ToShortDateString() != "1/1/1900")
            {
                command.Parameters.Add("@StopDate", SqlDbType.DateTime).Value = stopDate;
            }

            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }

            command.Prepare();
            reader = command.ExecuteReader();

            while (reader.Read())
            {
                returnString = reader[0].ToString();
            }
        }
    }
}

If I exec in query analyzer, output is the result of two diff queries (Please see image attached)

如果我在查询分析器中执行,输出是两个diff查询的结果(请参见附图)

DATABASE MESSAGE: The Goal was successfully Stopped.
DATABASE MESSAGE: The Goal was successfully created.

However the returnString in C# is capturing only message from the nested procedure and not calling procedure.

但是,C#中的returnString只捕获来自嵌套过程的消息,而不是调用过程。

DATABASE MESSAGE: The Goal was successfully Stopped.

How can I capture both or only calling procedure?

我如何捕获两个或仅捕获呼叫程序?

1 个解决方案

#1

Just as your cursor to the next row using SqlDataReaderReader.Read, you must also cursor to the next resultset using SqlDataReader.NextResult().

就像使用SqlDataReaderReader.Read将光标移动到下一行一样,您还必须使用SqlDataReader.NextResult()将光标移动到下一个结果集。

while (reader.Read())
{
    //Do something with first resultset
}
var ok = reader.NextResult();
if (ok)
{
    while (reader.Read())
    {
        //Do something with outer proc's resultset  
    }
}