Why is my stored procedure returning 0?

Question

I am trying to get the sum of the pointsEarned column and the sum of the pointsPossible column. I want to turn this into a percentage. My problem is that my SP always returns 0, even when there are rows with the given enrollmentId that have values for the previous columns.

What am I doing wrong?

ALTER PROCEDURE GetPercentage 
    @enrollmentId int
AS
BEGIN
    DECLARE @pointsEarned int;
    DECLARE @pointsPossible int;

    SET NOCOUNT ON;

    SELECT 
        @pointsEarned = CAST(SUM(pointsEarned) OVER() AS decimal), 
        @pointsPossible = CAST(SUM(pointsPossible) OVER() AS decimal)
    FROM 
        Assignments
    WHERE 
        enrollmentId = @enrollmentId

    RETURN @pointsEarned / @pointsPossible
END
GO

This is the database table:

I execute the stored procedure and passed in the enrollmentId of 69 and it still returns 0.

Why would you get rid of the return and replace with a `select`? It doesn't solve the problem and completely changes the usage of the SP. — Nick.Mc, Mar 07 '16 at 01:26

Mitch Wheat · Answer 1 · 2016-03-07T01:30:28.157

Integer division with denominator (pointsPossible) greater than numerator (pointsEarned) returns zero.

Write as (revised):

ALTER PROCEDURE GetPercentage 
    @enrollmentId int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        Percent = case when sum(pointsPossible) > 0 then (1.0 * sum(pointsEarned)) / sum(pointsPossible) else 0 end
    FROM 
        Assignments
    WHERE 
        enrollmentId = @enrollmentId
    GROUP BY
        enrollmentId 
END
GO

As @ Nick.McDermaid points out, since this procedure return a scalar value, it might be more appropriate to define it as a function rather than a store procedure.

score 2 · Answer 2 · answered Mar 07 '16 at 02:54

2

Use simple SUM without OVER.
RETURN can return only int. Integer division will always return 0 if your percentage is less than 100%, so multiply by 100 to return whole percents instead of ratio.
Check that you are not dividing by zero.

Procedure

ALTER PROCEDURE GetPercentage 
    @enrollmentId int
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @pointsEarned int;
    DECLARE @pointsPossible int;

    SELECT 
        @pointsEarned = SUM(pointsEarned),
        @pointsPossible = SUM(pointsPossible)
    FROM 
        Assignments
    WHERE 
        enrollmentId = @enrollmentId
    ;

    RETURN 
        CASE WHEN @pointsPossible <> 0
        THEN 100 * @pointsEarned / @pointsPossible
        ELSE 0 END;

END
GO

answered Mar 07 '16 at 02:54

Vladimir Baranov

31,799
5
53
90

@MitchWheat, I think I know what `SUM() OVER()` does. If I understood this question correctly, OP needs a plain sum (one number in one row), not the sum in multiple rows. – Vladimir Baranov Mar 07 '16 at 11:46
you mean like the answer I posted ? But good point about RETURN can only return an int. – Mitch Wheat Mar 07 '16 at 11:46
@MitchWheat, yes, your answer and mine are very similar. You decided to `SELECT` the result as a result set, which can hold non-integer value (`decimal` or `float`). I left original approach which `RETURNs` integer value. BTW, there is no need for `GROUP BY`, because of filter in `WHERE`. – Vladimir Baranov Mar 07 '16 at 11:51
RE: GROUP BY": yes, I know. It's there out of habit. – Mitch Wheat Mar 07 '16 at 11:51

Why is my stored procedure returning 0?

2 Answers2