2

I'm struggling here. This is my code

CREATE DEFINER=`****`@*****` PROCEDURE `setCountry`(
    IN `countryname` VARCHAR(100) CHARSET utf8mb4,
    IN `nationality` VARCHAR(100) CHARSET utf8mb4,
    IN `countryPrefix` INT(3)
)
    MODIFIES SQL DATA
INSERT INTO country(
    countryName,
    nationality,
    countryPrefix
)
SELECT
    @countryname
FROM
    (
    SELECT
        @countryname AS countryName,
        @nationality AS nationality,
        @countryprefix AS countryPrefix
) AS tmp
WHERE NOT EXISTS
    (
    SELECT
        countryName
    FROM
        country
    WHERE
        countryName = @countryname
)
LIMIT 1

And i'm trying to execute code with this sentence

call ccc.setCountry('Togo', 'Togolese', 228);

The ERROR it displays is this :

Error Code: 1136. Column count doesn't match value count at row 1

2 Answers 2

0

You beed to select 3 columns for4 the insert.

and @countryname and countryName are different variables

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `setCountry`(
    IN `countryname` VARCHAR(100) CHARSET utf8mb4,
    IN `nationality` VARCHAR(100) CHARSET utf8mb4,
    IN `countryPrefix` INT(3)
)
    MODIFIES SQL DATA
BEGIN
      SET @countryname := countryName;
      SET  @nationality := nationality;
      SET @countryprefix := countryPrefix;

    INSERT INTO country(
        countryName,
        nationality,
        countryPrefix
    )
    SELECT    
        countryname,
        nationality,
        countryPrefix
    FROM
        (
        SELECT
            @countryname AS countryName,
            @nationality AS nationality,
            @countryprefix AS countryPrefix
    ) AS tmp
    WHERE NOT EXISTS
        (
        SELECT
            countryName
        FROM
            country
        WHERE
            countryName = @countryname
    )
    LIMIT 1;
END$$

DELIMITER ;
1
0

Change from

SELECT @countryname FROM ( SELECT @countryname AS countryName, @nationality AS nationality, @countryprefix AS countryPrefix

to

SELECT
    @countryname AS countryName,
    @nationality AS nationality,
    @countryprefix AS countryPrefix

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.