Create Procedure in MySQL/MariaDB and define variable causes error #1064

Question

I'm trying to create a SP in mysql/mariadb with a declared variable inside - but I don't see, what's wrong with it!?!

DROP PROCEDURE IF EXISTS UpdateReceiverDevice;
DELIMITER $$

CREATE PROCEDURE `UpdateReceiverDevice`(IN `deviceIdentifier` VARCHAR(45), IN `deviceName` VARCHAR(45), IN `deviceLocation` VARCHAR(45), IN `informations` TEXT) NOT DETERMINISTIC MODIFIES SQL DATA SQL SECURITY DEFINER
BEGIN
    DECLARE receiverDeviceId AS INT(11) DEFAULT 0
    SET receiverDeviceId = (SELECT ID FROM ReceiverDevice WHERE DeviceIdentifier = deviceIdentifier);
    IF (receiverDeviceId > 0) BEGIN
        UPDATE ReceiverDevice SET Informations = informations WHERE ID = receiverDeviceId;
    ELSE
        INSERT INTO ReceiverDevice (DeviceName, DeviceLocation, DeviceIdentifier, Informations) VALUES(deviceName, deviceLocation, deviceIdentifier, informations);
    END IF
END $$

DELIMITER ;

MySQL is returning this error:

Ein oder mehrere Fehler sind aufgetreten während Ihre Anfrage verarbeitet wurde: Die folgende Abfrage ist fehlgeschlagen: "CREATE PROCEDURE UpdateReceiverDevice(IN deviceIdentifier VARCHAR(45), IN deviceName VARCHAR(45), IN deviceLocation VARCHAR(45), IN informations TEXT) NOT DETERMINISTIC MODIFIES SQL DATA SQL SECURITY DEFINER DECLARE receiverDeviceId AS INT(11) SET receiverDeviceId = (SELECT ID FROM ReceiverDevice WHERE DeviceIdentifier = deviceIdentifier) IF receiverDeviceId > 0 UPDATE ReceiverDevice SET Informations = informations WHERE ID = @receiverDeviceId ELSE INSERT INTO ReceiverDevice (DeviceName, DeviceLocation, DeviceIdentifier, Informations) VALUES(deviceName, deviceLocation, deviceIdentifier, informations) END IF"

MySQL meldet: #1064 - Fehler in der SQL-Syntax. Bitte die korrekte Syntax im Handbuch nachschlagen bei 'DECLARE receiverDeviceId AS INT(11) SET receiverDeviceId = (SELECT ID FROM Re' in Zeile 1

This is my server:

Server-Typ: MariaDB
Server-Version: 10.3.11-MariaDB-1:10.3.11+maria~bionic - mariadb.org binary distribution
Protokoll-Version: 10
Server-Zeichensatz: UTF-8 Unicode (utf8)

Every statement needs to be terminated. Including declarations and end ifs — P.Salmon
– P.Salmon, Commented May 4, 2019 at 16:33

Samuel Philipp · Accepted Answer · 2019-05-04 19:10:40Z

0

The hint in the comments "Every statement needs to be terminated" was very helpful.

DROP PROCEDURE IF EXISTS UpdateReceiverDevice;
DELIMITER $$

CREATE PROCEDURE `UpdateReceiverDevice`(IN `deviceIdentifier` VARCHAR(45), IN `deviceName` VARCHAR(45), IN `deviceLocation` VARCHAR(45), IN `informations` TEXT) NOT DETERMINISTIC MODIFIES SQL DATA SQL SECURITY DEFINER 
BEGIN
    DECLARE receiverDeviceId INT(11) DEFAULT 0;
    SELECT ID INTO receiverDeviceId FROM ReceiverDevice WHERE DeviceIdentifier = deviceIdentifier;

    IF receiverDeviceId > 0 THEN
        UPDATE ReceiverDevice SET Informations = informations WHERE ID = receiverDeviceId;
    ELSE
        INSERT INTO ReceiverDevice (DeviceName, DeviceLocation, DeviceIdentifier, Informations) VALUES(deviceName, deviceLocation, deviceIdentifier, informations);
    END IF;
END $$

DELIMITER ;

edited May 4, 2019 at 19:10

Samuel Philipp

11.1k12 gold badges41 silver badges58 bronze badges

answered May 4, 2019 at 18:49

Homer Simpson

465 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create Procedure in MySQL/MariaDB and define variable causes error #1064

This is my server:

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

This is my server:

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related