How to return multiple records from multiple tables bu a single stored procedure having parameters?

Question

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???

Answer 1

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

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

Answer 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

Answer 3

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

Answer 4

[link text]goog[1]