CODEWITHSUNDEEP
sqlstored-procedures
Suresh M
Suresh M

Reputation: 29

Combine results of multiple queries as one result in a SQL Server stored procedure

I need some help with a stored procedure. It contains a loop which runs a Select query. What I get is 3 tables with result if it loops thrice. How can I combine the results as one table?

The procedure is as below: 

CREATE PROCEDURE [dbo].[spGetRndQuestions]  
    @ExamCode Nvarchar(60)
AS
BEGIN
    Declare @NosQues Int, @Catgry nvarchar(50)

    DECLARE CategCursor CURSOR FOR 
      (Select Category  From tblExamDetail Where ExamCode = @ExamCode)

    OPEN CategCursor 

    FETCH NEXT FROM CategCursor INTO @Catgry

    WHILE @@FETCH_STATUS  = 0
    BEGIN
        SET @NosQues = (Select NoOfQues from tblExamDetail Where ExamCode=@ExamCode AND Category=@Catgry)

        SELECT TOP(@NosQues) QM.QuestionID, QM.QuestionDesc, QM.QuestionMarks, QM.Answer1, QM.Answer2, QM.Answer3, QM.Answer4 FROM tblQuestionMaster QM 
            INNER JOIN tblExamMaster EM ON QM.Dept = EM.Dept AND QM.Location = EM.Location AND QM.QuesModule = EM.ExamModule 
                Where EM.ExamCode=@ExamCode AND QM.Category =@Catgry 
                Order by NEWID()

        /*SELECT TOP (@NosQues) QuestionID,QuestionDesc,Answer1,Answer2,Answer3,Answer4,QuestionMarks    from [dbo].[tblQuestionMaster] Where Category=@Catgry AND 
        Order by NEWID() */

        FETCH NEXT FROM CategCursor INTO @Catgry
    END
CLOSE CategCursor
DEALLOCATE CategCursor
END

Thanks, your help is truly appreciated.

Upvotes: 0

Views: 3135

Answers (2)

CElliott
CElliott

Reputation: 452

Hamlet does answer the question. However, the query you posted could be optimized to eliminate the need for the cursor or table variable. The following code should do that nicely:

CREATE PROCEDURE [dbo].[spGetRndQuestions]  
    @ExamCode Nvarchar(60)
AS

SELECT
    --A.[category],
    B.*
FROM tblExamDetail A
CROSS APPLY (
    SELECT TOP (A.[NoOfQues])
        QM.QuestionID,QM.QuestionDesc,QM.QuestionMarks,
        QM.Answer1,QM.Answer2,QM.Answer3,QM.Answer4
    FROM tblQuestionMaster QM
    INNER JOIN tblExamMaster EM
    ON  QM.Dept = EM.Dept
        AND QM.Location = EM.Location
        AND QM.QuesModule = EM.ExamModule
    WHERE   EM.ExamCode = A.[ExamCode]
        AND QM.Category = A.[Category]
    ORDER BY NEWID()
) B
WHERE A.[ExamCode] = @ExamCode

Upvotes: 0

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Create the table variable in start of your SP of appropriate structure of your return table. In each iteration insert selected data to that table. After deallocating cursor select from that table.

CREATE PROCEDURE [dbo].[spGetRndQuestions]  
@ExamCode Nvarchar(60)   AS
BEGIN
    Declare @NosQues Int, @Catgry nvarchar(50)

    DECLARE @tbl TABLE(QuestionID int, QuestionDesc ....)

    DECLARE CategCursor CURSOR  FOR (Select Category  From tblExamDetail Where ExamCode=@ExamCode)
    OPEN CategCursor 
    FETCH NEXT FROM CategCursor INTO @Catgry
    WHILE @@FETCH_STATUS  = 0
    BEGIN
        SET @NosQues = (Select NoOfQues from tblExamDetail Where ExamCode=@ExamCode AND Category=@Catgry)

        INSERT INTO @tbl
        SELECT TOP(@NosQues) QM.QuestionID, QM.QuestionDesc, QM.QuestionMarks, QM.Answer1, QM.Answer2, QM.Answer3, QM.Answer4 FROM tblQuestionMaster QM 
            INNER JOIN tblExamMaster EM ON QM.Dept = EM.Dept AND QM.Location = EM.Location AND QM.QuesModule = EM.ExamModule 
                Where EM.ExamCode=@ExamCode AND QM.Category =@Catgry 
                Order by NEWID()

        /*SELECT TOP (@NosQues) QuestionID,QuestionDesc,Answer1,Answer2,Answer3,Answer4,QuestionMarks    from [dbo].[tblQuestionMaster] Where Category=@Catgry AND 
        Order by NEWID() */

        FETCH NEXT FROM CategCursor INTO @Catgry
    END
CLOSE CategCursor
DEALLOCATE CategCursor

SELECT * FROM @tbl
END

Upvotes: 3

Related Questions