By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SELECT VERSION();
| VERSION() |
|---|
| 8.0.11 |
DROP PROCEDURE IF EXISTS `SaveCity`;
DROP TABLE IF EXISTS `cities`;
CREATE TABLE IF NOT EXISTS `cities` (
`CityID` BIGINT,
`StateID` BIGINT,
`CityName` VARCHAR(50)
);
INSERT INTO `cities` (`CityID`, `StateID`, `CityName`)
VALUES (1, 1, 'MyCity');
CREATE PROCEDURE `SaveCity` (
IN `inStateID` varchar(2),
IN `inCityName` varchar(30),
OUT `outCityID` varchar(3)
)
BEGIN
-- DECLARE cityCode VARCHAR(3) DEFAULT 0;
DECLARE cityCode VARCHAR(3) /* DEFAULT NULL */;
DECLARE maxCityID VARCHAR (3);
/* Test whether CityName already exists */
SELECT CityID INTO cityCode FROM cities WHERE StateID = inStateID AND CityName = inCityName;
IF cityCode IS NULL THEN
/* Fetch Max CityID */
SELECT Max(CityID) INTO maxCityID FROM cities;
/* Increment CityID counter */
SET cityCode = maxCityID + 1;
INSERT INTO cities VALUES (cityCode, inStateID, inCityName);
END IF;
SET outCityID = cityCode;
-- SELECT @outCityID;
SELECT outCityID;
END;
CALL`SaveCity`(1, 'MyNewCity', @`newCityID`);
| outCityID |
|---|
| 2 |