Problems to create MySQL Stored Procedure

Question

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!

Gordon Linoff · Accepted Answer · 2016-06-07 01:36:25Z

1

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 very similr to a MySQL reserved word.

edited Jun 7, 2016 at 1:36

answered Jun 6, 2016 at 14:45

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Luis G. Lino Over a year ago

Thank you for your help and advice, this solved my problem!! In 7 minutes I will check your answer as correct! :)

Collectives™ on Stack Overflow

Problems to create MySQL Stored Procedure

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related