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
執行：
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)
輸出：
Student Name: Waqar
Lowest Marks in Final Term of this Semester: 50
Highest Marks in Final Term of this Semester: 50
Waqar是最后一條記錄，以上記錄不列印。我知道它必須與追加有關，但我在追加時感到困惑，因為我的代碼也有輸入。
uj5u.com熱心網友回復：
通常你只會回傳一個結果集，呼叫程式會顯示資料。所以
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/411237.html
標籤：