Hi,

I have a function that returns the error No data - zero rows fetched, selected, or processed. I've searched on Google and nobody seems to have found a solution. The code of my stored function goes like this, maybe someone will be able to help me find a workaround:

CREATE FUNCTION get_trans_header(No_auction INT UNSIGNED)
RETURNS INT UNSIGNED
BEGIN
DECLARE int_no_transfert INT UNSIGNED DEFAULT 0;

SELECT TrFH_no_transfert INTO int_no_transfert FROM Transfert_fed_header WHERE (TrFH_no_encan = No_encan) AND (TrFH_start_date IS NULL) AND (TrFH_end_date IS NULL) LIMIT 1;

IF int_no_transfert IS NULL THEN
INSERT INTO Transfert_fed_header(TrFH_no_auction) VALUES(No_auction);
SELECT LAST_INSERT_ID() INTO int_no_transfert;
END IF;

RETURN(int_no_transfert);
END//

The purpose of this function is to find a record who's start and end dates are null. If we find one, we return the id (TrFH_no_transfert) else we insert a new record and get the new id with LAST_INSERT_ID().

I'm running this on a MySQL 5.0.24a-community-nt.

Any help would be greatly appreciated,

Denis Dupere.

Hi,
I'm not an expert in MySQL 5, but i also know how the PL/SQL for oracle works to avoid zero rows fetched. It is not eficient but to minimize unknown bugs.

In your code
Of coz the result would be NO result or YES result, and No data - zero rows fetched will be thrown if NO result.
To avoid that

Yes it helps a little on your code..

Hi pakcik,

Sounds logical, I'll try that.

Thank you for your help!!

Hi again,

I've tried your solution and it worked. Thanks a million!!!

Hi folks,

Today I ran into a similar situation on a Mysql 5.0.27 box
Here is how I made it work:

.................
DECLARE int_no_transfert INT UNSIGNED DEFAULT 0;

SET int_no_transfert = (SELECT TrFH_no_transfert FROM Transfert_fed_header WHERE (TrFH_no_encan = No_encan) AND (TrFH_start_date IS NULL) AND (TrFH_end_date IS NULL) LIMIT 1);

IF int_no_transfert !=0 THEN
-- it means select returned something and our local variable has changed
-- so here I treat the duplicate case....
ELSE
-- select returned no resultset... thus leaving untouched the local variable : int_no_transfert (0)
-- so, no duplicate.. proceed with insert.. or stuff
END