mercredi 18 août 2021

MySQL IF NOT EXISTS ... THEN statement

I come from a SQL Server background, but am now developing on MySQL. I have a stored procedure with which I'd like to contain the following construct:


DROP PROCEDURE IF EXISTS CreateLicence;

DELIMITER //
CREATE PROCEDURE CreateLicence
    (OUT pLicenceID INT,
    INOUT pUsrID INT,
    INOUT pMethodCode VARCHAR(10),
    INOUT pPaymentReference VARCHAR(50),
    INOUT pPaymentAmount DECIMAL(13, 2),
    INOUT pProductCode VARCHAR(5),
    OUT pMessage VARCHAR(200)
    )
/*
    Creates a new user record, which needs to have its email verified.
*/
entire_sproc:
BEGIN
    DECLARE vRecCount INT;
    
    
    START TRANSACTION;

    -- is the MethodCode valid?
    IF (pMethodCode <> 'PAYPAL') THEN
    BEGIN
        ROLLBACK;
        SET pMessage = 'Invalid MethodCode.';
        SET pLicenceID = NULL;
        LEAVE entire_sproc;
    END IF;
    
    
    -- is the ProductCode valid?
    IF (NOT EXISTS (SELECT ProductCode FROM Product WHERE ProductCode = pProductCode)) THEN
    BEGIN
        ROLLBACK;
        SET pMessage = 'Invalid ProductCode.';
        SET pLicenceID = NULL;
        LEAVE entire_sproc;
    END IF;


    INSERT INTO Licence
            (UsrID,
            MethodCode,
            Status,
            PaymentReference,
            PaymentDateTime,
            PaymentAmount,
            ProductCode)
    VALUES
            (pUsrID,
            pMethodCode,
            'UNAPPLIED',
            pPaymentReference,
            NOW(),
            pPaymentAmount,
            pProductCode);


    SET pLicenceID = (SELECT LAST_INSERT_ID());
    
    
    COMMIT;
    
    
END;
//

DELIMITER ;

I'm getting this error at compilation time:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'IF;
    
    
    -- is the ProductCode valid?
    IF (NOT EXISTS (SELECT ProductC...' at line 27

What is it that's not allowed about this SQL construct? How do I test for the existence of a record in an IF...THEN statement?

Aucun commentaire:

Enregistrer un commentaire