Question :
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
Answer :
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 ;
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