MySQL Forums :: Stored Procedures :: Function to get max score.


Advanced Search

Re: Function to get max score.
Posted by: Peter Brawley ()
Date: May 21, 2016 08:21PM

> 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.

Options: ReplyQuote


Subject Views Written By Posted
Function to get max score. 2995 B Aneja 05/21/2016 07:50PM
Re: Function to get max score. 374 Peter Brawley 05/21/2016 08:21PM
Re: Function to get max score. 361 B Aneja 05/22/2016 01:21AM
Re: Function to get max score. 388 Peter Brawley 05/22/2016 02:52AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.