I have a function which takes student id as input and returns the average grade of the student.
I'm not sure why I'm getting this bound error. Is it because of the FETCH next command?
The CourseEnrollment table has 2 columns, studentid, courseid and finalgrade
CREATE FUNCTION dbo.PersonalAverage ( @StudentId VARCHAR(20) )
RETURNS DECIMAL AS
BEGIN
DECLARE @AverageGrade DECIMAL(5,2)
DECLARE @TotalMarks INT
DECLARE @NumberOfCourses INT
SET @AverageGrade=0
SET @TotalMarks=0
SET @NumberOfCourses=0
DECLARE CalculateAverage CURSOR FOR
SELECT FinalGrade
FROM CourseEnrollment
WHERE @StudentId=dbo.CourseEnrollment.StudentId
OPEN CalculateAverage
WHILE @@FETCH_STATUS=0
BEGIN
SET @NumberOfCourses = @NumberOfCourses + 1
SET @TotalMarks = @TotalMarks + dbo.CourseEnrollment.FinalGrade
FETCH NEXT FROM CalculateAverage INTO @TotalMarks
END
IF @NumberOfCourses>0
BEGIN
SET @AverageGrade = @TotalMarks/@NumberOfCourses
END
CLOSE CalculateAverage
DEALLOCATE Calculateaverage
RETURN @AverageGrade
END;
Corrected Version:
CREATE FUNCTION dbo.PersonalAverage ( @StudentId VARCHAR(20) )
RETURNS DECIMAL(5,2) AS
BEGIN
DECLARE @FinalGrade INT
DECLARE @AverageGrade DECIMAL(5,2)
DECLARE @TotalMarks DECIMAL(5,2)
DECLARE @NumberOfCourses INT
SET @AverageGrade=0
SET @TotalMarks=0
SET @NumberOfCourses=0
SET @FinalGrade=0
DECLARE CalculateAverage CURSOR FOR
SELECT dbo.CourseEnrollment.FinalGrade
FROM dbo.CourseEnrollment
WHERE @StudentId=dbo.CourseEnrollment.StudentId AND dbo.CourseEnrollment.FinalGrade IS NOT NULL
OPEN CalculateAverage
FETCH NEXT FROM CalculateAverage INTO @FinalGrade
WHILE @@FETCH_STATUS = 0
BEGIN
SET @NumberOfCourses = @NumberOfCourses + 1
SET @TotalMarks = @TotalMarks + @FinalGrade
FETCH NEXT FROM CalculateAverage INTO @FinalGrade
END
IF @NumberOfCourses>0
BEGIN
SET @AverageGrade = @TotalMarks/@NumberOfCourses
END
CLOSE CalculateAverage
DEALLOCATE Calculateaverage
RETURN @AverageGrade
END;
I think you need an initial
FETCH NEXT FROM CalculateAverage INTO @TotalMarks
between
OPEN CalculateAverage
and
WHILE @@FETCH_STATUS=0
What Gordon said. The use of a cursor here is misguided, you can easily do this sort of calc using SQL aggregation. AVG...GROUP BY.
I suspect the error is in SET @TotalMarks = @TotalMarks + dbo.CourseEnrollment.FinalGrade as you there really is no reference to dbo.CourseEnrollment available in that line.
Note: I am having some trouble making the formatting stick.
with you declarations:
DECLARE @FinalGrade DECIMAL(5,2)
then OPEN CalculateAverage; FETCH NEXT FROM CalculateAverage INTO @FinalGrade
WHILE @@FETCH_STATUS=0
BEGIN
SET @NumberOfCourses = @NumberOfCourses + 1
SET @TotalMarks = @TotalMarks + @FinalGrade -- @FinalGrade
FETCH NEXT FROM CalculateAverage INTO @FinalGrade
END