the_lonely_doge the_lonely_doge - 3 months ago 21
MySQL Question

Create procedure MySQL Error

So I've been trying to do this simple procedure but for some reason when I run the code in a call statement it brings up an error saying "[

ERROR in query 1
] Result consisted of more than one row". I've ran each of the statements in the procedure individually and they work perfectly, returning one row. So obviously I must have a syntax error somewhere. If someone could shed light on this that would be great.

DELIMITER ;;

CREATE PROCEDURE userInfo(in uID int, out fullname varchar(50), out occupation varchar(50), out NumOrders int(11), out DVDCount int(11), out VehicleCount int(11))
BEGIN
SELECT concat(firstname, ' ', lastname) INTO fullname FROM users WHERE userid = uID;

SELECT occupation into occupation FROM occupation JOIN users ON users.occupationid = occupation.occupationid WHERE userid = uID;

SELECT count(*) INTO NumOrders FROM orderitem
JOIN orders ON orders.orderid = orderitem.orderid
JOIN users ON orders.userid = users.userid
WHERE users.userid = uID;

SELECT count(*) INTO DVDCount FROM userDVD WHERE userid = uID;

SELECT count(*) INTO VehicleCount FROM userVehicle WHERE userid = uID;
END;;

Answer
DELIMITER ;;

CREATE PROCEDURE userInfo(in uID int, out fullname varchar(50), out occupation varchar(50), out NumOrders int(11), out DVDCount int(11), out VehicleCount int(11))
BEGIN
SELECT concat(firstname, ' ', lastname) INTO fullname FROM users WHERE userid = uID LIMIT 1;

SELECT COALESCE(occupation, 'no occupation') into occupation FROM occupation JOIN users ON users.occupationid = occupation.occupationid WHERE userid = uID LIMIT 1;

SELECT count(*) INTO NumOrders FROM orderitem
JOIN orders ON orders.orderid = orderitem.orderid
JOIN users ON orders.userid = users.userid
WHERE users.userid = uID;

SELECT count(*) INTO DVDCount FROM userDVD WHERE userid = uID;

SELECT count(*) INTO VehicleCount FROM userVehicle WHERE userid = uID;
END;;
Comments