Executing stored procedure prints only the last record-CodePudding

CREATE PROCEDURE DISPLAY3
    @semester int,
    @StdName varchar(15) OUTPUT,
    @LMarks int OUTPUT,
    @HMarks int OUTPUT
AS
BEGIN
    SELECT DISTINCT
        @StdName = StdName, 
        @HMarks = MAX(sm.MarksObtained), 
        @LMarks = MIN(sm.MarksObtained)
    FROM
        std s
    INNER JOIN
        stdcourseteacher sct ON sct.StdID = s.StdID
    INNER JOIN
        Exam e ON e.SemID = @semester AND e.Topic = 'Final-Term'
    INNER JOIN
        StdMarks sm ON sm.StdID = sct.StdID AND e.ExamID = sm.ExamID
    GROUP BY
        s.StdName, sm.ExamID
END

Execution:

DECLARE @StdName varchar(15)
DECLARE @LMarks int
DECLARE @HMarks int

EXEC DISPLAY3 6, @StdName OUTPUT, @LMarks OUTPUT, @HMarks OUTPUT

PRINT 'Student Name: '   CONVERT(varchar(15), @StdName)   CHAR(13)
PRINT 'Lowest Marks in Final Term of this Semester: '   CONVERT(varchar(10), @LMarks)
PRINT 'Highest Marks in Final Term of this Semester: '   CONVERT(varchar(10), @LMarks)

Output:

Student Name: Waqar

Lowest Marks in Final Term of this Semester: 50
Highest Marks in Final Term of this Semester: 50

Waqar is last record, the above records are not printing. I know it has to do something with appending but I got confused while appending because my code has input as well.

CodePudding user response：

Normally you would just return a resultset, and the calling program would display the data. So

create or alter procedure DISPLAY3 @semester int
as
begin
 select StdName, HMarks = Max(sm.MarksObtained), LMarks = Min(sm.MarksObtained)
 from std s
 inner join stdcourseteacher sct on sct.StdID = s.StdID
 inner join Exam e on e.SemID = @semester and e.Topic = 'Final-Term'
 inner join StdMarks sm on sm.StdID = sct.StdID and e.ExamID = sm.ExamID
 group by s.StdName
end