MySQL Forums
Forum List  »  Stored Procedures

Function to get max score.
Posted by: B Aneja
Date: May 21, 2016 07:50PM

The function takes TWO input parameters {student_id and event_id} and returns INT.I need to validate the input student_id and event_id to make sure they are valid. I want to validate this by doing COUNT(*) from both Student and Event table. If both student_id and event_id are valid it should return MAX(SCORE) for this function.

I AM A NEWBIE TO SQL AND MYSQL AND AM ASKING YOU TO PLEASE HELP DEBUG THIS FUNCTION IF YOU CAN AND PLEASE LET ME KNOW WHAT YOU THINK IS WRONG WITH IT. I am completely out of sorts trying to get this right.




delimiter $$

DROP FUNCTION IF EXISTS max_score$$

CREATE FUNCTION max_score(in_student_id INT
,in_event_id INT
)

RETURNS VARCHAR(1)

BEGIN

DECLARE sql_stmt VARCHAR(1000);
DECLARE l_count INT;


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

SELECT @sql_stmt;

SELECT CONCAT('StudentId is :', @l_count);


SET @sql_stmt = CONCAT('SELECT COUNT(*) INTO @l_count'
,' FROM GRADE_EVENT'
,' WHERE EVENT_ID=in_event_id'
)
;
SELECT @sql_stmt;

SELECT CONCAT('Event_id is :', @l_count);


IF @l_count > 0 THEN

SET @sql_stmt = CONCAT( 'SELECT MAX(SCORE)'
,'FROM SCORE'
,'WHERE STUDENT_IN = in_student_id'
,'AND EVENT_ID = in_event_id');

RETURN sql_stmt;

END$$

delimiter ;

Options: ReplyQuote


Subject
Views
Written By
Posted
Function to get max score.
3078
May 21, 2016 07:50PM
398
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.