phpmyadmin Question posted in
The official documentation can be found here.

stored procedure in mysql, cannot figure out the syntax error – Phpmyadmin

this is my proc, and below is my error, apart form the numerous ; missing, what am I Missing on line 2??

CREATE PROCEDURE sp_insertOnReject
@id int
AS 
BEGIN
START TRANSACTION;
UPDATE content_manager.submission SET state = 'Rejected' WHERE id = @id;

INSERT INTO content_manager.submission_rejected
    (id int,link_initial varchar,email varchar,id_category int ,state varchar,link_final varchar,ip_sub int ,sub_date datetime) 
SELECT
    submission.id, submission.link_initial, submission.email, submission.id_category, submission.state, submission.link_final, submission.ip_sub, submission.sub_date
FROM
    content_manager.submission
WHERE content_manager.submission.id = @id
COMMIT;
END


#1064 - Syntax error in the query SQL near '@id int
AS
BEGIN
START TRANSACTION' line 2

i really don’t understand what syntax error am I missing, pls help

P.S: I’m doing my proc on phpMyAdmin, if that changes something…

Using @Arulkumar solution, the error shifts :

CREATE PROCEDURE sp_insertOnReject
(
    IN id int
)
BEGIN
START TRANSACTION;
    UPDATE content_manager.submission SET state = 'Rejected' WHERE id = @id;

    INSERT INTO content_manager.submission_rejected
    (id, link_initial, email, id_category, state, link_final, ip_sub, sub_date) 
    SELECT submission.id, submission.link_initial, submission.email, submission.id_category, submission.state, submission.link_final, submission.ip_sub, submission.sub_date
    FROM content_manager.submission
    WHERE content_manager.submission.id = @id;
    
COMMIT;
END

the error:

#1064 - Syntax error in the query SQL near '' line 6

still no solution

Tags:

2

Answers


  1. 0

    you copied your code from another database probably SQL SERVER. mysql handles such things different

    DELIMITER $$
CREATE PROCEDURE sp_insertOnReject
(IN_id int)

BEGIN
START TRANSACTION;
SET @id = _id;
UPDATE content_manager.submission SET state = 'Rejected' WHERE id = @id;

INSERT INTO content_manager.submission_rejected
    (id ,link_initial ,email ,id_category ,state ,link_final ,ip_sub,sub_date) 
SELECT
    submission.id, submission.link_initial, submission.email, submission.id_category, submission.state, submission.link_final, submission.ip_sub, submission.sub_date
FROM
    content_manager.submission
WHERE content_manager.submission.id = @id;
COMMIT;
END$$
DELIMITER ;
    Login or Signup to reply.
  2. 0

    Please find the procedure with out any error. There are lot of issues in your procedure.

    • Parameter declaration,
    • AS added,
    • Insert into variable contain the data type,
    • missed semi colon.

    CREATE PROCEDURE sp_insertOnReject
(
    IN InputId int
)
BEGIN
START TRANSACTION;
    UPDATE content_manager.submission SET state = 'Rejected' WHERE id = InputId;

    INSERT INTO content_manager.submission_rejected
    (id, link_initial, email, id_category, state, link_final, ip_sub, sub_date) 
    SELECT submission.id, submission.link_initial, submission.email, submission.id_category, submission.state, submission.link_final, submission.ip_sub, submission.sub_date
    FROM content_manager.submission
    WHERE content_manager.submission.id = InputId;
    
COMMIT;
END
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search