Urgent help needed here for a school Project

Question

Pls I need help in this stored procedure in sql server 2008 for calculating GPA for student for a project I'm creating. Based on the formula to calculate gpa = Total Grade Points/Total Credit hours, this is what I did;

I have to tables Course and Result and I intend to pick the credit hours of a particular course (foreign key) in the Result table from the Course table. I came up with this code below and it's given me a result of -6 and some strange messages which is ofcourse not normal.

Please I would like someone to check it out below and find out what I'm not doing right. Many thanks in advance.

CREATE PROCEDURE [dbo].[GPAComputation]

(

@StudentId int

)

AS

BEGIN

DECLARE @GPA decimal(4,2)

DECLARE @CreditHours int

SELECT @CreditHours = (SELECT c.CreditHours FROM [Course]c WHERE c.CourseId = (SELECT r.CourseId FROM [Result]r WHERE r.CourseId = c.CourseId))

SELECT @GPA = CONVERT(decimal(4,2), SUM(@CreditHours * CAST(CASE r.Grades WHEN 'A' THEN 5 WHEN 'B' THEN 4 WHEN 'C' THEN 3 WHEN 'D' THEN 2

WHEN 'E' THEN 1 ELSE 0 END AS int )) / SUM(@CreditHours))

FROM [Result]r

WHERE

r.StudentId = @StudentId

END

Sam Hobbs · Answer

If you still have not figured it out perhaps you could make some sample data that you can upload.

Vulpes · Answer

With no great confidence I'd suggest changing this statement: SELECT @GPA = CONVERT(decimal(42) SUM(@CreditHours * CAST(CASE r.Grades WHEN 'A' THEN 5 WHEN 'B' THEN 4 WHEN 'C' THEN 3 WHEN 'D' THEN 2 WHEN 'E' THEN 1 ELSE 0 END AS int )) / SUM(@CreditHours)) FROM [Result]r WHERE r.StudentId = @StudentId to this: SELECT @GPA = CONVERT(decimal(42) SUM(@CreditHours * (CASE r.Grades WHEN 'A' THEN 5 WHEN 'B' THEN 4 WHEN 'C' THEN 3 WHEN 'D' THEN 2 WHEN 'E' THEN 1 ELSE 0 END)) / CAST(SUM(@CreditHours) AS decimal)) FROM [Result]r WHERE r.StudentId = @StudentId The cast to decimal on the second operand is to prevent the division been done using integer arithmetic which will truncate the fractional part. The cast to int on the CASE expression result should be unnecessary as it's already an int.

Kacey Ezerioha · Answer

I've gone through it a couple of times and still could not find out what the problem is. I'll accept whatever corrections you are able to come up with. Thank you

Vulpes · Answer

In that case I don't see how you can get a negative result unless there's something wrong with the expression. The expression looks OK to me but it is complex. I'd try breaking it up by assigning the CASE expression to a temporary variable and see if that makes any difference to the result. If nothing else you might be able to figure out more easily where the problem lies.

Kacey Ezerioha · Answer

The dummy data I filled the tables with are all positive numbers (Integers) and they don't allow nulls. Pls help me

Vulpes · Answer

Is it possible that CreditHours could be negative or null for some records?