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