CODEWITHSUNDEEP
mysqlstored-procedures
Undermine2k
Undermine2k

Reputation: 1491

MySQL stored procedure troubles

Basically i'm going to run this procedure anytime a student enrolls/drops a course. I'm trying to set student_total = # of students in a course, then update that corresponding section with (student_total + 1) i'm having trouble finding good documentation for stored procedures. I'm getting an error on my Declare student_total int; line. What am i not doing correct?

DELIMITER $$

CREATE PROCEDURE `mydb`.`update_seats` (IN section_ID varchar(20))
BEGIN

SET @section_id=section_id;

DECLARE student_total int;
-- count the number of students in the course --

SET student_total = SELECT count(student_ID) from course
WHERE section_ID = @section_id;


Update Section SET students_enrolled = (student_total + 1) 
WHERE section_ID = @section_id;




END

Upvotes: 0

Views: 116

Answers (2)

mmdemirbas
mmdemirbas

Reputation: 9158

Problems

Error 1

From MySql documentation:

DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements.

So, you should move DECLARE... statement before the SET @section_id... statement.

Error 2

You are trying to select a value into a variable using invalid snytax! You should use SELECT ... INTO instead of SET ... = SELECT ... (which is invalid syntax).

Removing Redundancy

No need to assign parameter (section_ID) to a global variable (@section_ID). You can simply change the parameter name to avoid name collision with section.section_ID column.

Solution

DELIMITER ;;

CREATE PROCEDURE `update_seats` (IN p_section_ID VARCHAR(20))
BEGIN
    DECLARE student_total INT;

    SELECT  count(student_ID)
    INTO    student_total
    FROM    course
    WHERE   section_ID = p_section_ID;

    UPDATE  section
    SET     students_enrolled = (student_total + 1)
    WHERE   section_ID = p_section_ID;
END;;

DELIMITER ;

Upvotes: 2

ConsultantChris
ConsultantChris

Reputation: 1

You're using the command line tool, yes?

It would be helpful to know the error message you received but based off the code you posted I don't see where you reset the delimiter command after the BEGIN...END block.

Taken from http://dev.mysql.com/doc/refman/5.1/en/stored-programs-defining.html

Upvotes: 0

Related Questions