Subquery returns more than 1 row

[solomon_13000]

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','[e-mail]','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.

[pritaeas]

The stored proc cannot determine that the output returns only one row. You can try adding LIMIT 1 to your subquery.

[solomon_13000]

Is it the best method and considered to be a safe solution?

[solomon_13000]

The stored proc cannot determine that the output returns only one row. You can try adding LIMIT 1 to your subquery.

My stored procedure doesn't retrieve the correct id.

[smantscheff]

Your query

SELECT id FROM vwRetrieveCorporateDetails WHERE companyName=companyName AND rocNo=rocNo AND country=country

has the same names on both sides of the equation signs. Which means that all records are retrieved. You have to use variable names which are not the same as the column names.

[solomon_13000]

Exactly. WHERE companyName=companyName is true. So I made the changes on the stored procedure param name WHERE companyName=compName......and it works.