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:
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:
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.