Re: Function to get max score.

[Peter Brawley]

> AM A NEWBIE ...

No need to shout.

Without your tables and their data, we cannot debug the func for you.

Your approach to debugging is correct: insert stubs to find out where it is going awry, and track what thy contain. But MySQL functions do not support straight SELECTs like that; you need to Select Into user variables, eg ...

SELECT @sql_stmt INTO @sql1;
...
SELECT @sql_stmt INTO @sql2;

etc., then Select those vars when the func returns.

Aside from that, I see three instances of the same errors in your code. The 1st sql should be ...

SET @sql_stmt = CONCAT( 'SELECT COUNT(*) INTO @l_count'
,' FROM STUDENT'
,' WHERE STUDENT_ID=', in_student_id );

so that the func picks up the value of the in_student_id param.

Likewise for the 2nd & 3rd sql assignments.

(In each case this can also be done with explicit params.)

When you've debugged the syntax, add in

PREPARE stmt from @sql_stmt;
EXECUTE stmt;
DROP PREPARE stmt;



Edited 1 time(s). Last edit at 05/22/2016 02:52AM by Peter Brawley.