CODEWITHSUNDEEP
mysqlsql
Bisoux
Bisoux

Reputation: 522

Placement of a delete sql in a mysql procedure

I need to delete records from a staging table in a stored procedure. I then need to insert records into the same table. This must be done prior to using a cursor that loops through the records.

DELIMITER //

CREATE PROCEDURE Get_Employee_Records()
BEGIN

DECLARE rollingSum INT DEFAULT 0;
DECLARE theMonth varchar(3);

-- Delete & insert on Employee must be done prior to the select 

DECLARE mycursor CURSOR FOR  SELECT Tgrowth,TYear,myMONTH FROM Employee;
         -- cursor body here
    CLOSE mycursor;

END //

DELIMITER ;

When i place my insert & delete statements in the commented section i get

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE mycursor CURSOR FOR  SELECT Tgrowth,TYear,myMONTH FROM Employee' at line 63

Where would be the right place to place the insert & delete sql in the procedure. I could probably do this in a separate procedure but I want to know if i am missing something here.

Upvotes: 0

Views: 148

Answers (2)

P.Salmon
P.Salmon

Reputation: 17640

In a begin...end block declarations must appear before any other code so wrap the cursor section in a begin..end like so (btw you need to fix the code).

begin
    DECLARE mycursor CURSOR FOR  SELECT Tgrowth,TYear,myMONTH FROM Employee;
         -- cursor body here
    open mycursor;
    CLOSE mycursor;
end;

There is almost never a reason to use cursors in sql - are you sure your approach is appropriate?

Upvotes: 1

Atif
Atif

Reputation: 2210

I am not very familiar with MYSQL but I think you DELETE and INSERT should come after Cursor declaration and and before opening and closing the cursor.

Upvotes: 0

Related Questions