MySQL Stored Procedure unhelpful error

Question

MySQL gives me "right syntax to use near ''" as an error in response to my SP..

attached SQL is throwing the above error, no idea why, the SP needs to be put into the left pane, but couldn't get it to save without that.

http://www.sqlfiddle.com/#!2/075db/3

Any idea why its doing it?

score 0 · Accepted Answer · answered Feb 24 '14 at 22:00

Try this:

CREATE PROCEDURE Upsert_Page(
  pageID smallint,
  pageTitle varchar(50),
  pageName varchar(50),
  pageViewable tinyint,
  pageDeleted tinyint,
  pageMetaData varchar(5000),
  pageParent smallint,
  pageRequired tinyint,
  pageAdmin tinyint
)
BEGIN
  IF (SELECT 1 = 1 FROM Page WHERE Page.ID = pageID) THEN

    UPDATE `Page`
        SET `Page`.`Title` = pageTitle, 
            `Page`.`Name` = pageName, 
            `Page`.`IsViewable` = pageViewable, 
            `Page`.`IsDeleted` = pageDeleted, 
            `Page`.`MetaData` = pageMetaData, 
            `Page`.`ParentID` = pageParent, 
            `Page`.`IsRequired` = pageRequired, 
            `Page`.`IsAdmin` = pageAdmin
    WHERE `Page`.`ID` = pageID;

  ELSE
    begin
    DECLARE last_id int;
    INSERT INTO `Page`(`Title`, `Name`, `IsViewable`, `IsDeleted`, 
                       `MetaData`, `ParentID`, `IsRequired`, `IsAdmin`) 
    VALUES (pageTitle, pageName, pageViewable, pageDeleted, 
            pageMetaData, pageParent, pageRequired, pageAdmin);

    SET last_id = LAST_INSERT_ID();

    INSERT INTO `PageWidget`(`ID`,`WidgetID`,`PageID`) 
    SELECT 
        null, `Widget`.`ID`, last_id
    FROM `Widget`
    JOIN `WidgetType` ON `Widget`.`WidgetTypeID` = `WidgetType`.`ID`
    WHERE `WidgetType`.`IsUnique` = 1;
  end;
  END IF;
END;
/

Now it compiles fine, see this demo : http://www.sqlfiddle.com/#!2/a3d58
(you will need to scroll to the bottom of the left panel)

There were some small errors - missing semicolons, missing backtick characters etc.
One remark: IF ... THEN ... END doesn't need BEGIN ... END block, just:

IF condition THEN
   statements 
   .....
   .....
ELSE
  statements
  ....
  ....
END IF;

Hi Kordirko, thank you for your response, I've tried your example in phpmyadmin sql tab, and received an error "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 '/", that error still apparent when removing the "/" at the end of the query too.. is there a difference between sqlfiddle and phpmyadmin? — skh, Feb 24 '14 at 22:37
Please read this link: http://stackoverflow.com/questions/8080681/store-procedures-in-phpmyadmin to learn how to create stored procedures using phpmyadmin. You need to set a delimiter (for example $$), and place it after last `end`. Please study this link for details. — krokodilko, Feb 24 '14 at 22:44

MySQL Stored Procedure unhelpful error

1 Answers1