CODEWITHSUNDEEP
mysqlsqlstored-procedures
Mark
Mark

Reputation: 71

MySql COUNT(*) is different in Stored Procedure

I am working on a 'grading' system, and am trying to make sure that a person is not able to submit a grade twice by using a stored procedure that will check if a person has graded a particular item before allowing a new grade to be saved. The odd thing is, I am passing a user ID and object ID, but when my stored procedure selects the COUNT(*), I get the wrong number.

Here is my stored procedure:

CREATE PROCEDURE `ADD_GRADE`(IN objectID int, IN grader int)
BEGIN
DECLARE gradeCount INT;

SELECT COUNT(*)
FROM GRADES
WHERE Obj_ID = objectID AND Grader = grader
INTO gradeCount;

IF gradeCount <= 0 THEN INSERT INTO Grades (Obj_ID, Grader) VALUES (objectID, grader);
END IF;

The IF statement is working, but for some reason my gradeCount appears to be ignoring the Grader ID and just checking based upon the Obj_ID. I've added selects to be sure my parameters are staying at the correct value and they are. If I copy just the select and do it elsewhere manually, I get the correct number.

Though new to MySql, I'm not new to SQL itself. Am I just going crazy? Or am I doing something wrong?

Thanks

Upvotes: 4

Views: 8119

Answers (2)

Scott Bruns
Scott Bruns

Reputation: 1981

Even if this code worked (I don't know why it does not) it is not the proper way to make sure something is only entered once.

The proper way is to apply a unique constraint on the objectID and grader columns. Then try inserting the row. If the row inserts then the values are unique. If you get a unique violation then the values have already been entered.

If you do not have unique constraints available you should lock the table to make sure no other updates are happening between your commands.

Upvotes: 12

Kibbee
Kibbee

Reputation: 66132

My guess is because MySQL is case insensitive for field names, and the code 

Grader = grader

is just comparing the column with itself, which is always true. you may need to rename the parameter you are passing in so that it doesn't have the same name as an existing column. I usually have all my stored procedure arguments proceeded with __ (douple underscore) so I don't run into situations like this.

Upvotes: 7

Related Questions