The object DDL statement contain syntax error - mysql

Question

I have a question. I am making an api with node, express, mysql. When I want to create a procedure in mysql that allows me to add or edit elements to the database, it tells me "The object DDL statement contain syntax error ". Does anyone see the error? Any help is appreciated, ty

CREATE PROCEDURE transactionAddorEdit (
IN _id INT,
IN _type VARCHAR(11),
IN _date,
IN _name VARCHAR(40),
IN  _amt decimal(13,2)
)

BEGIN 
    IF _id = 0 THEN
        INSERT INTO transactions (type, date, name, amt)
        VALUES (_type, _date, _name, _amt)
        SET _id = LAST_INSERT_ID();
    ELSE
        UPDATE transactions
        SET 
            type=_type,
            date=_date,
            name=_name,
            amt=_amt
            WHERE id=_id;
    END IF;
    
    SELECT _id AS id;
END

You should post the exact error message, it will usually contain a line number or the next syntax _after_ the failure. — Chris Schaller, Jan 24 '22 at 02:50

Chris Schaller · Answer 1 · 2022-01-25T02:27:41.310

You are missing the Data Type specifier for the _date parameter. The types are specified for the other parameters so I won't assume what the correct type in your schema is for this column.

However the error message should have returned the following information:

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 '
IN _name VARCHAR(40),
IN  _amt decimal(13,2)
)

BEGIN 
    IF _id = 0 THEN
    ' at line 4

From this you can tell that the error was immediately before IN _name VARCHAR(40), and that is the _date parameter.

Once your date parameter is correct, the next common syntax issue with Stored Procedures in MySql is that you may need to escape your query terminator with the DELIMITER statement, this requirement depends on your execution pipeline.

So you may need something like this:

DELIMITER // ;

CREATE PROCEDURE transactionAddorEdit (
IN _id INT,
IN _type VARCHAR(11),
IN _date datetime(6),
IN _name VARCHAR(40), 
IN  _amt decimal(13,2)
)

BEGIN 
    IF _id = 0 THEN
        INSERT INTO transactions (type, date, name, amt)
        VALUES (_type, _date, _name, _amt);    
        SELECT _id = LAST_INSERT_ID();
    ELSE
        UPDATE transactions
        SET 
        
            type=_type,
            date=_date,
            name=_name,
            amt=_amt
            WHERE id=_id;
    END IF;
    
    SELECT _id AS id;
END//

This is necessary because the CREATE PROCEDURE command is itself a command,

The object DDL statement contain syntax error - mysql

1 Answers1