3

I am attempting to recreate a stored procedure (since I can't edit the body). I called SHOW CREATE PROCEDURE to use the same format as the original stored procedure but when I attempt to recreate it I get the following errors:

ERROR 1064 (42000): 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 '' at line 11

ERROR 1064 (42000): 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 organization_id BIGINT(20) UNSIGNED' at line 1

ERROR 1064 (42000): 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 lobby_pod_id BIGINT(20) UNSIGNED' at line 1

Here's the code:

CREATE DEFINER=`lms`@`10.0.0.%` PROCEDURE `create_organization`(
    IN admin_username VARCHAR(255),
    IN organization_name VARCHAR(100)
)
BEGIN
    DECLARE admin_user_id BIGINT(20) UNSIGNED;
    DECLARE organization_id BIGINT(20) UNSIGNED;
    DECLARE lobby_pod_id BIGINT(20) UNSIGNED;

    SELECT ID, account INTO admin_user_id, organization_id 
    FROM users 
    WHERE username = admin_username;

    INSERT INTO pods (`title`, `description`, `owner`, `scene`)
    VALUES (CONCAT(organization_name, " Village"),
            CONCAT("General meeting space and hub for ", organization_name, " students and teachers."),
            admin_user_id,
            " Village"
    );
END

I pasted into SQL Fiddle and got the same result, although pasting into MySQL Syntax Check gave me the thumbs-up. I'm sure it's a simple miss but it isn't that obvious to me.

Jaydip Jadhav
  • 12,179
  • 6
  • 24
  • 40
Pecam
  • 33
  • 1
  • 3

1 Answers1

3

You are missing the delimiter definition before and after the stored proc definition:

If you use the mysql client program to define a stored program containing semicolon characters, a problem arises. By default, mysql itself recognizes the semicolon as a statement delimiter, so you must redefine the delimiter temporarily to cause mysql to pass the entire stored program definition to the server.

To redefine the mysql delimiter, use the delimiter command. [...] The delimiter is changed to // to enable the entire definition to be passed to the server as a single statement, and then restored to ; before invoking the procedure. This enables the ; delimiter used in the procedure body to be passed through to the server rather than being interpreted by mysql itself.

Since the stored proc definition and body was ok, syntax chack gave you the thumbs up, but the code would not run properly in your client.

Use the following skeleton for defining a stored procedure:

delimiter //
create procedure ...
...
end
//
delimiter ;
Shadow
  • 33,525
  • 10
  • 51
  • 64
  • That was it - only thing I changed from your skeleton to make it work was I dropped the trailing "delimeter ;" – Pecam Mar 22 '16 at 16:20
  • I would add it back, unless you would like to terminate all commands by // through your session. If my answer helped to resolve the issue, then pls mark it as accepted to let other viewers know that your questions has already been answered. – Shadow Mar 22 '16 at 16:31
  • Ah - that would explain why the rest of my commands were not terminating (facepalm) – Pecam Mar 22 '16 at 16:39