CODEWITHSUNDEEP
mysql
user1739825
user1739825

Reputation: 820

mysql stored function - in varchar and return varchar - Error : 1172

I could not figure out what went wrong with my stored function.

select batch as bach from test where mfg_code = 'BC-7A1-5' group by batch;

When I run the script above and I am able to get the number of rows as expected.

However I am not able with the similar script below:-

DELIMITER $$
DROP FUNCTION IF EXISTS `testdata1970_05`.`listbatch` $$
CREATE FUNCTION `listbatch`(mfgnum VARCHAR(24)) RETURNS VARCHAR(10)

BEGIN
DECLARE bach VARCHAR(10);
SELECT batch into bach FROM test WHERE mfg_code = mfgnum group by batch;
RETURN bach;
END $$

DELIMITER ;

And it returns error:

"ERROR 1172 (42000): Result consisted of more than one row"

And here is my query below:

select listbatch("BC-7A1-5");

Upvotes: 2

Views: 11581

Answers (1)

Incognito
Incognito

Reputation: 3094

Your error message is telling you that more than one row is being returned by your query, and the resultset cannot be stored in a simple variable, as the variable you defined can hold only one value!

Upvotes: 1

Related Questions