CODEWITHSUNDEEP
mysqlsqlstored-procedures
Christofer Klassen
Christofer Klassen

Reputation: 157

SQL Stored Procedure Syntax error missing 'end'

I've been working on an SQL stored procedure and am very new to the process. I'm trying to write a procedure that retrieves a value from one table and inserts it into another as part of a new row.

I'm using MySQL Workbench, and it's giving me an error at the line

WHERE blockId = blk;

As part of the following code:

CREATE DEFINER=`root`@`localhost` PROCEDURE `add_userblock`(IN user INT(11), IN blk INT(11))
BEGIN
    -- Determine how many blocks you can add
    DECLARE blockNum INT DEFAULT -1;
    SELECT addCount INTO blockNum
        FROM block
        WHERE blockId = blk;

    -- Determine if the block already exists for the user
    DECLARE entryExists INT DEFAULT 0;
    SELECT COUNT(*) INTO entryExists
        FROM userblock
        WHERE blockId = blk AND userId = user;

    IF (entryExists = 0)
        -- This is a new entry
        BEGIN
        INSERT INTO userblock (userId, blockId, num) VALUES (user, blk, blockNum);
        SELECT LAST_INSERT_ID() as 'id';
        END
    ELSE
        -- This is an existing entry
        BEGIN
        UPDATE userblock
            SET num = (num + blockNum)
            WHERE userId = user AND blockId = blk;
        END
END

The error is "Syntax error: missing 'end'". I'm not entirely sure what it means, and I'm having trouble solving the issue.

Thanks, I appreciate your time!

Upvotes: 2

Views: 10488

Answers (2)

Christofer Klassen
Christofer Klassen

Reputation: 157

With help, I was able to solve the issue (at least as far as compiling goes) with the following code:

CREATE DEFINER=`root`@`localhost` PROCEDURE `add_userblock`(IN user INT(11), IN blk INT(11))
BEGIN
    DECLARE blockNum INT DEFAULT -1;
    DECLARE entryExists INT DEFAULT 0;

    -- Determine how many blocks you can add
    SELECT addCount INTO blockNum
        FROM block
        WHERE blockId = blk;

    -- Determine if the block already exists for the user
    SELECT COUNT(*) INTO entryExists
        FROM userblock
        WHERE blockId = blk AND userId = user;

    IF entryExists = 0
    THEN
        -- This is a new entry
        INSERT INTO userblock (userId, blockId, num) VALUES (user, blk, blockNum);
        SELECT LAST_INSERT_ID() as 'id';
    ELSE
        -- This is an existing entry
        UPDATE userblock
            SET num = (num + blockNum)
            WHERE userId = user AND blockId = blk;
        SELECT LAST_INSERT_ID() as 'id';
    END IF;
END

The issue seemed to be a combination of two things:

  1. I was using the wrong IF ELSE syntax for MySQL workbench's stored procedures
  2. I needed to declare my variables at the top of the procedure

Thanks for the support!

Upvotes: 4

Andreas Wederbrand
Andreas Wederbrand

Reputation: 40021

Add END IF after the ELSE-block

CREATE 
DEFINER=`root`@`localhost` 
PROCEDURE `add_userblock`(
  IN user INT(11), 
  IN blk INT(11)
)
BEGIN
-- Determine how many blocks you can add
DECLARE blockNum INT DEFAULT -1;
SELECT addCount INTO blockNum
  FROM block
 WHERE blockId = blk;

-- Determine if the block already exists for the user
DECLARE entryExists INT DEFAULT 0;
SELECT COUNT(*) INTO entryExists
  FROM userblock
 WHERE blockId = blk
   AND userId = user;

IF (entryExists = 0)
    -- This is a new entry
    BEGIN
    INSERT INTO userblock (userId, blockId, num) VALUES (user, blk, blockNum);
    SELECT LAST_INSERT_ID() as 'id';
    END
ELSE
    -- This is an existing entry
    UPDATE userblock
       SET num = (num + blockNum)
     WHERE userId = user AND blockId = blk;
END IF
END

Upvotes: 2

Related Questions