MySQL Forums
Forum List  »  Stored Procedures

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
3090
May 21, 2016 07:50PM
Re: Function to get max score.
411
May 21, 2016 08:21PM
403
May 22, 2016 01:21AM


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.