0

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.

3
Contributors
5
Replies
6
Views
5 Years
Discussion Span
Last Post by solomon_13000
0

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

0

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.

Edited by solomon_13000: n/a

1

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.

Votes + Comments
Totally missed this.
0

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

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.