CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
ShoeLace1291
ShoeLace1291

Reputation: 4698

Why is my stored procedure returning 0?

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:

enter image description here

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

Upvotes: 0

Views: 2573

Answers (2)

Vladimir Baranov
Vladimir Baranov

Reputation: 32695

  1. Use simple SUM without OVER.

  2. 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.

  3. 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

Upvotes: 2

Mitch Wheat
Mitch Wheat

Reputation: 300549

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.

Upvotes: 2

Related Questions