ERROR 1329 (02000): No data - zero rows fetched, selected, or processed
That error is not about your
RETURN; it's this:
fetch cur1 into pid;
When the cursor runs out of data, this error is thrown. This could be because the
SELECT query declared in the cursor didn't find any rows, or because it did find rows but as you iterated through it, you read past the last row. If nothing happened when the cursor ran out of data, how would you ever break out of
You need to trap the error with a handler, which will prevent execution of the procedure from terminating, and set a variable you can use to break out of the loop.
At the top, with the other variables:
DECLARE done TINYINT DEFAULT 0;
Then, after declaring but before opening the cursor:
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
Inside your loop:
fetch cur1 into pid;
IF done = 1 THEN LEAVE read_loop; END IF;
This should solve your immediate problem.
Your function modifies the database, so at the top, you should have this:
MODIFIES SQL DATA
...although, really, this would be more appropriately written as a stored procedure rather than a function. MySQL supports non-deterministic stored functions that modify the database, you there are potential issues with the binary log used for point-in-time recovery or replication that can pop up.
To process all of the data rather than one value at a time, you could use nested cursors -- the outer cursor fetching whatever data you're feeding into this function and the inner cursor -- inside an additional
END block doing the logic you've got, above with values fetched in the outer cursor. This requires resetting of the
done value back to 0 after each iteration of the inner block so that the other block doesn't stop prematurely.
The inner subquery is unnecessary, since the cursor could be declared with...
FROM records r JOIN Programs p ON p.programID = r.usageProgramID
...but, in fact, this entire function seems like it may be unnecessary.
In SQL, it's typically best if you you tell the database what needs to be done ("declarative"), as opposed to how to do it ("procedural").
Thinking too procedurally is an SQL antipattern.
I don't know your column names in the 'login' table, so you'll need to correct those, but consider the following query. As far as I can tell, it does exactly what the function does, for all logins and all matching records.
INSERT INTO login_apps(`sid`, `programName`)
SELECT l.session_id, p.programName
FROM logins l
JOIN records r ON r.usageUser = l.user_name
AND r.usageWhen >= l.start_time
AND r.usageWhen <= l.end_time
AND r.usageProgramID != ""
JOIN Programs p ON p.programID = r.usageProgramID;
For future maintenance of the "login_apps" table, a trigger on the "records" table could look up the related login and write the new entries into "login_apps" every time "records" has an insert or an update... or when the stop time is logged on the "login" table, an update trigger could read "records" and insert into "login_apps".
Final thought, it would probably be better design if you stored the program ID rather the program name in the login_apps table... unless the "records" table actually has an auto_increment primary key, in which case, that would be the value you should really store in login_apps.