What is proper syntax for returning a string with MySQL function?

Question

I followed the example provided in our class material and made a function that returns a count. Now I am trying to make a function that would get me the full name of a user by the same principle I made the count function but I keep getting this error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 4

CREATE FUNCTION getAuthorFullName (authorID INT)
RETURNS CHAR(45)
BEGIN
DECLARE author CHAR(45);
SELECT CONCAT(first, ' ',last) FROM person as fullName
JOIN user ON user.idPerson = fullName.idPerson
WHERE idUser LIKE authorID
INTO author;
RETURN author;

I checked the SELECT query on its own and it retrieves the correct full name of the author when I pass the authors ID manually so I just have to get it working in a function where I can simply pass the author's ID.

Did you change the DELIMITER?

Strawberry
– Strawberry

2018-12-14 13:21:41 +00:00
Commented Dec 14, 2018 at 13:21 — Strawberry
– Strawberry, Commented Dec 14, 2018 at 13:21

Juan Carlos Oropeza · Accepted Answer · 2018-12-14 14:30:49Z

1

INTO go after the value.

Also if idUser is integer use = operator. LIKE id for strings

SELECT CONCAT(first, ' ',last) INTO author
FROM person as fullName
JOIN user ON user.idPerson = fullName.idPerson
WHERE idUser = authorID
;

Here is a sql demo

DROP FUNCTION IF EXISTS getAuthorFullName;

CREATE FUNCTION getAuthorFullName (authorID INT)
RETURNS CHAR(45)
BEGIN
DECLARE author CHAR(45) ;
SELECT CONCAT('first', ' ','last' ) INTO author;
RETURN author;
END;

SELECT getAuthorFullName(1);

edited Dec 14, 2018 at 14:30

answered Dec 14, 2018 at 13:10

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

ADyson Over a year ago

dev.mysql.com/doc/refman/8.0/en/… says "MySQL Server doesn't support the SELECT ... INTO TABLE Sybase SQL extension. Instead, MySQL Server supports the INSERT INTO ... SELECT standard SQL syntax"

Madhur Bhaiya Over a year ago

@ADyson select .. into.. is a different beast. You are mixing two different things here: dev.mysql.com/doc/refman/8.0/en/select-into.html

Madhur Bhaiya Over a year ago

However, one problem with the solution is that SELECT..INTO specifically requires the query to return 1 row only. Otherwise it throws error; without any idea of table data from OP, a LIMIT 1 is required at the end for the query to perform without any errors.

ADyson Over a year ago

@MadhurBhaiya didn't know that, thanks. I see the difference - selecting into a variable (supported) vs selecting into a table (not supported). And of course this is the former case.

Juan Carlos Oropeza Over a year ago

@ADyson yes those are two different things

|

Collectives™ on Stack Overflow

What is proper syntax for returning a string with MySQL function?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related