Luis G. Lino Luis G. Lino - 5 months ago 7
SQL Question

Problems to create MySQL Stored Procedure

I am trying to create this procedure:

DELIMITER //
CREATE PROCEDURE PA_INCLUDE_SETTING
(IN SettingType INT(10),
IN SettingName INT(10),
IN Active INT(1),
OUT result INT(1))
BEGIN
DECLARE exist INT;
SELECT COUNT(*) INTO exist
FROM tbSetting
WHERE nmSetting = SettingName
AND cdSettingType = SettingType;
IF (exist > 0) THEN
DECLARE active INT;
SELECT cdActive INTO active
FROM tbSetting
WHERE nmSetting = SettingName
AND cdTypeSetting = SettingType;
IF (active = 0) THEN
UPDATE tbSetting
SET cdActive = 1
WHERE nmSetting = SettingName
AND cdSettingType = SettingType;
SET result = -1;
ELSE
SET result = -2;
END IF;
ELSE
INSERT INTO tbSetting
(cdSettingType, nmSetting, cdActive)
VALUES (SettingType, Setting, Active);
SET result = 0;
END IF;
END //
DELIMITER ;


I am using this table:

CREATE TABLE tbSetting
(
cdSettingType INT(10) NOT NULL,
cdSetting INT(10) IDENTITY(1,1) NOT NULL,
nmSetting VARCHAR(30),
cdActive INT(1),
CONSTRAINT pk_cdSetting PRIMARY KEY (cdSettingType, cdSetting )
)


And I am receiving this error message:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE active INT;
SELECT cdActive
FROM tbSetting
WHERE n' at line 13


This is the first time I create an Stored Procedure with this kind of complexity and I don't understand why this error is appearing.

Thank you in advance for any help!

Answer

Declarations should immediately follow the begin block. So just move the declaration up to the beginning of the function:

BEGIN
    DECLARE exist INT;
    DECLARE active INT;

    . . .

By the way, exist is a very bad name for a variable because it is a MySQL reserved word.

Comments