How do I return a value from another column by matching an input from a function?-CodePudding

Table

CREATE DEFINER=`root`@`localhost` FUNCTION `GetGPA`(sName VARCHAR(45)) RETURNS float
    DETERMINISTIC
BEGIN
DECLARE grade FLOAT;
DECLARE sName VARCHAR(45);

SELECT GPA INTO grade
FROM student
WHERE studentName = sName;

RETURN grade;
END

I did the function and it returned null: Stored Function

I tried returning a student's GPA using a function inputting a student's name.

CodePudding user response：

Use:

CREATE FUNCTION GetGPA (sname varchar(45)) 
  RETURNS float DETERMINISTIC
BEGIN
 DECLARE grade FLOAT;
   SELECT GPA INTO grade
   FROM student
   WHERE StudentName = sName;
  RETURN grade;
END

https://dbfiddle.uk/AF9iO_l8

Reference

The only problem in your function is DECLARE sName VARCHAR(45); remove that part and your function is ok

CodePudding user response：

when you used Into key word,that means you create temp table. so far you should be use a variable that its type equal with GPA column and set value on in,finally return it:

CREATE DEFINER=`root`@`localhost` FUNCTION `GetGPA`(sName VARCHAR(45)) RETURNS float
    DETERMINISTIC
BEGIN
DECLARE @grade FLOAT;
DECLARE sName VARCHAR(45);

SELECT @grade=GPA  
FROM student
WHERE studentName = sName;

RETURN @grade;
END