Ad
Executing Stored Procedure Prints Only The Last Record
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.
Ad
Answer
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
Ad
source: stackoverflow.com
Related Questions
- → How to make Laravel use my class instead of native PDO?
- → SQL: simple custom column in select doesn't work?
- → How to execute Stored Procedure from Laravel
- → Which database engine type should be specified for Microsoft SQL Database in Laravel?
- → How to troubleshoot PDOException?
- → laravel sql server stored procedure output
- → Issue with converting a date using Carbon
- → SQL microsoft query to Laravel 4.2
- → General error 20018 Cannot Continue the file execution because the session is in the Kill state
- → List names of all available MS SQL databases on server using python
- → Variable which replace DB of name in SSMS
- → Java: database connection. Where is my mistake?
- → How Can I use "Date" Datatype in sql server?
Ad