Subquery returns more than 1 row

I created a stored procedure as below:

DELIMITER$$
CREATE PROCEDURE updateCorporateDetails(IN companyName VARCHAR(30), IN rocNo VARCHAR(20),
IN address VARCHAR(30), IN postalCode INT, IN state VARCHAR(20), IN country VARCHAR(20),
IN contactNo VARCHAR(20), IN email VARCHAR(20), IN fax VARCHAR(20), IN lastUpdate DATETIME,
OUT total INT)
BEGIN
    DECLARE var1 INT;
    SET @var1 = (SELECT id FROM vwRetrieveCorporateDetails WHERE companyName=companyName AND rocNo=rocNo AND country=country);
    SELECT @var1;
END$$
DELIMITER;

Then I attempted to call the stored procedure as below:

MySQL Syntax (Toggle Plain Text)
CALL updateCorporateDetails('Tanja','444','No. 6 Underground Street',33333,'SEL','MY',
'012-6666666','admin@tanjong.com','03-6666666',NOW(),@total);

Guess what I keep getting the following error:

“Subquery returns more than 1 row”

But the actual fact is when I execute the query as below:

MySQL Syntax (Toggle Plain Text)
SELECT id FROM vwRetrieveCorporateDetails
WHERE companyName='Tanja' AND rocNo='444' AND country='MY';

It returns only 1 row.

Question: What could have possibly went wrong?.

Thank You.

WHERE companyName=companyName is always going to be true, for every row

regardless of whether companyName is a column or a procedure parameter

(hint: you need to distinguish between them)

:slight_smile:

You are absolutely right.