如何从具有参数的单个存储过程返回多个表中的多个记录？

Suppose my stored procedure is:

假设我的存储过程是:

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'sp_QB_descriptionwise_select')
    BEGIN
        DROP  Procedure  sp_QB_descriptionwise_select
    END
GO
CREATE Procedure sp_QB_descriptionwise_select
    (
        @description nvarchar(max)
    )
AS

declare @type int
SELECT     Question.questionid, Question.question, Question.answer, Question.typeOfQuestion, QuestionBank.description
FROM         QuestionBank INNER JOIN
                      Question ON QuestionBank.questionid = Question.questionid
WHERE     (QuestionBank.description = @description)

SELECT     @type = Question.typeOfQuestion
FROM         QuestionBank INNER JOIN
                      Question ON QuestionBank.questionid = Question.questionid
WHERE     (QuestionBank.description = @description)

if @type=1
begin
SELECT     OptionQuestion.option1, OptionQuestion.option2, OptionQuestion.option3, OptionQuestion.option4
FROM         OptionQuestion INNER JOIN
                      Question ON OptionQuestion.questionid = Question.questionid
end
GO

How can i store the records n in what way???

我怎么能以什么方式存储记录?

3 个解决方案

#1

Using IDataReader / ExecuteReader, you should be able to use:

使用IDataReader / ExecuteReader,您应该能够使用:

    using (IDataReader reader = cmd.ExecuteReader())
    {
        do
        {
            while (reader.Read())
            {
                /* row */
            }
        } while (reader.NextResult()); /* grid */
    }

#2

Call your stored procedure with the DataSetAdapter.Fill() method. It will populate your dataset with all resulting tables. If you use a typed dataset make sure to set up table mappings (populate the DataSetAdapter.TableMappings collection). Details to be found on MSDN

使用DataSetAdapter.Fill()方法调用存储过程。它将使用所有结果表填充数据集。如果使用类型化数据集,请确保设置表映射(填充DataSetAdapter.TableMappings集合)。详细信息可在MSDN上找到

#3

DataSetCommand.selectCommand accepts parameter as sqlcommand.commandText,sqlConnction how to pass parameters in that case???

DataSetCommand.selectCommand接受参数为sqlcommand.commandText,sqlConnction如何在这种情况下传递参数???

#1