Creating MySQL Stored Procedure

Question

I am using Aqua Data Studio 20.6 to create a MySQL stored procedure. The following is my procedure, I have named it sp_GetObjIDByProType:

( IN prono char(15), IN imgtype char(8), OUT objid bigint )
BEGIN
    SELECT ObjectID INTO objid FROM images WHERE ProNumber = prono AND DocType = imgtype LIMIT 20;
END

When I try to create the stored procedure, I get this error message:

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 imgtype char(8) )
( OUT objid bigint )
BEGIN
    SELECT ObjectID INTO objid F' at line 3

However, it appears as if creating the task was actually successfully (sp_GetObjIDByProType appears under 'Procedures'). I can view and alter this created stored procedure in another window (resulting stored procedure in 'Alter' window). Does this mean that creation of the stored procedure was actually successful, or is there something wrong with what I did?

Pay attention: the code itself and its citate in the error message differs. — Akina, Apr 15 '21 at 15:47
@Akina Thanks for your response. I noticed that, do you know why there was a difference? I tried creating some other stored procedures and got no errors like this. Is it safe to ignore the first error? — MERNboy, Apr 15 '21 at 15:53

score 0 · Accepted Answer · answered Apr 15 '21 at 16:47

Looks like your procedure was created. However, the LIMIT 20 in the query does not make sense. You are returning the selected ObjectID in the OUT parameter which can hold only one value. Yet you limit the query to 20 rows.

If you want to return max 20 rows, return the rows as a result set (leave out the out parameter and the INTO objid), or limit the rows to just one row.

Creating MySQL Stored Procedure

1 Answers1