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)

Answer 1

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 ;