MySQL Stored Procedure with Cursor Error

Question

I am trying to write a MySQL stored procedure that (1) inserts values into an Employee table and (2) takes the new employee's department number, scans the Project table for any projects that the department works on and (3) inserts those projects with the employee's SSN into the Works_On table.

I am attempting to do this with a cursor, but keep running into syntax errors that I cannot figure out. The current error is at the point that I am declaring the cursor, but I have no idea how to fix it. I have tried quite a few things and am hoping that someone will be able to see the mistake.

    Use Company
DELIMITER //

Create Procedure SP_Insert_NewEmployee 
(
    IN fname varchar(30),
    IN minit char(1),
    IN lname varchar(30),
    IN ssn char(9),
    IN bdate date,
    IN address varchar(50),
    IN sex char(1),
    IN salary decimal(10,1),
    IN super_ssn char(9),
    IN dno int
)
Begin
Declare projectNumber Integer;
Declare myCursor2 = CURSOR FOR
    SELECT Pnumber
    FROM PROJECT
    Where Dnum = @dno;

#Insert into Employee
Insert into EMPLOYEE 
    (
        Fname,
        Minit ,
        Lname ,
        Ssn ,
        Bdate ,
        Address ,
        Sex,
        Salary ,
        Super_ssn,
        Dno
    )
Values
    (
        $fname ,
        $minit ,
        $lname,
        $ssn ,
        $bdate ,
        $address ,
        $sex ,
        $salary ,
        $super_ssn ,
        $dno 
    );
    END

#Find projects by new employee's dept




OPEN myCursor2;

FETCH NEXT 
    FROM myCursor2 
    INTO 
        projectNumber


WHILE @@FETCH_STATUS = 0

BEGIN
 Insert Into WORKS_ON
 Values
    (
        ssn,
        projectNumber,
        0
    )

 FETCH NEXT 
 FROM myCursor2 
    INTO 
        projectNumber
END

CLOSE myCursor2;
DEALLOCATE myCursor2;
END
END
//
DELIMITER ;

Better to add comment rather than answer, first you have to declare cursor as 'DECLARE cur_saving_acc CURSOR FOR', the code is messed up so not sure if other issues not exist — Shushil Bohara
– Shushil Bohara, Commented Sep 22, 2016 at 4:32
That was the first issue....I mixed up MySQL and SQL Server cursor syntax. Thanks! — cisstudent123
– cisstudent123, Commented Sep 22, 2016 at 4:34

Drew · Accepted Answer · 2016-09-22 06:25:56Z

We undeleted your question on my request so you wouldn't flail around out there confused. Your code had several syntax errors. Some were just made up sql, others were missing commas and errant dollar signs. Hopefully this helps.

USE Company;
DROP PROCEDURE IF EXISTS SP_Insert_NewEmployee;
DELIMITER //
CREATE PROCEDURE SP_Insert_NewEmployee 
(
    IN fname varchar(30),
    IN minit char(1),
    IN lname varchar(30),
    IN ssn char(9),
    IN bdate date,
    IN address varchar(50),
    IN sex char(1),
    IN salary decimal(10,1),
    IN super_ssn char(9),
    IN dno int
)
Begin
DECLARE done INT DEFAULT FALSE;
Declare projectNumber Integer;
Declare myCursor2 CURSOR FOR SELECT Pnumber FROM PROJECT Where Dnum = dno;

#Insert into Employee
Insert into EMPLOYEE 
    (   Fname,
        Minit ,
        Lname ,
        Ssn ,
        Bdate ,
        Address ,
        Sex,
        Salary ,
        Super_ssn,
        Dno
    )
Values
    (   fname ,
        minit ,
        lname,
        ssn ,
        bdate ,
        address ,
        sex ,
        salary ,
        super_ssn ,
        dno 
    );

#Find projects by new employee's dept

OPEN myCursor2;

do_something: LOOP
    FETCH myCursor2 INTO projectNumber;
    IF done THEN
      LEAVE do_something;
    END IF;
    Insert Into WORKS_ON Values (ssn,projectNumber,0);
END LOOP; 

CLOSE myCursor2; 

END // 
DELIMITER ;

Follow the manual page closely here: CURSORS.

Collectives™ on Stack Overflow

MySQL Stored Procedure with Cursor Error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related