SQL Server & C# Stored Procedure & Divide by Zero Exception

Question

So first here is my C# code and then comes the stored procedure.

public DataTable GetCourseHighPass(String tmpCourse)
{
    command.Connection = OpenConnection();

    try
    {
        command.CommandText = "exec GetCourseCompletions @tmpCourse = '" + tmpCourse + "'";
        SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
        dataAdapter.Fill(dataTable);
        return dataTable;
    }
    catch (Exception)
    {
        throw new Exception("There are no VG's for this course.");
    }
    finally
    {
        command.Connection.Close();
    }
}

And here is my stored procedure.

create procedure GetCourseCompletions
   @tmpCourse nvarchar(30)
as
   select (count(pnr) * 100 / (select count(pnr) 
                               from HasStudied  
                               where courseCode = @tmpCourse 
                                 and count(pnr) =)) as VGPrecentage 
   from HasStudied 
   where grade >= 5 
     and courseCode = @tmpCourse
go

The problem is that if there are no students with a high pass I will get a divide by zero exception. Looking for suggestions on how to catch this exception so the program does not crash or even better to re-write the stored procedure so it does not get an exception in the first place.

Thank you for your assistance!

Select your counts into variables in the stored procedure and, if the count is zero, return whatever you think it should return rather than performing the calculation. — Eric J., May 20 '15 at 00:02
This will be vulnerable to sql injection attacks. You should use parameterized queries. — Joel Coehoorn, May 20 '15 at 05:13

score 2 · Accepted Answer · answered May 20 '15 at 00:18

Do what Eric said:

    DECLARE @count int
    Set @count = (select count(pnr) from HasStudied where courseCode = @tmpCourse and count(pnr) =...)
    IF @count = 0
    BEGIN
        SELECT 0 as VGPrecentage
    END
    ELSE
    BEGIN
        select (count(pnr)*100 / @count) as VGPrecentage from HasStudied where grade >= 5 and courseCode = @tmpCourse
    END

score 0 · Answer 2 · answered May 20 '15 at 05:02

I suggest you to use this kind of query instead of yours that will handle NULL values and Zero values:

SELECT 
    CASE WHEN part * total <> 0 THEN part * 100 / total ELSE 0 END
FROM (
    SELECT SUM(CASE WHEN grade > 5 THEN 1.00 ELSE 0.00 END) As part, SUM(1.00) as total
    FROM HasStudied
    WHERE courseCode = @tmpCourse) t

SQL Server & C# Stored Procedure & Divide by Zero Exception

2 Answers2