You have an error in your SQL syntax (CREATE PROCEDURE)

Question

I try to create function in mariadb (latest stable). Want to get all data by company id (int)

CREATE FUNCTION getByCompId (cid INT)
RETURN (SELECT * FROM companies WHERE id=cid);

end when i try to create, i get error:

SQL Error [1064] [42000]: (conn=111) 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 'RETURN (SELECT * FROM cdr WHERE id=cid)' at line 2

Function may return one scalar value (not multi-column rowset). And function's returning datatype must be specified explicitly (RETURNS attribute). Study carefully: mariadb.com/kb/en/create-function — Akina
– Akina, Commented Dec 28, 2020 at 10:55

GMB · Accepted Answer · 2020-12-28 11:07:01Z

1

You can't return a tabular resultset from a function, only scalar values. Instead, you can use a stored procedure:

delimiter //
create procedure getbycompid (cid int)
begin
    select * from companies where id = cid;
end //
delimiter ;

And then you call the procedure like so:

call getbycompid(1);

Since we have a one-liner, we could shorten the code as just:

create procedure getbycompid (cid int)
    select * from companies where id = cid
;

edited Dec 28, 2020 at 11:07

answered Dec 28, 2020 at 10:57

GMB

224k25 gold badges103 silver badges151 bronze badges

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

2 Comments

mocart Over a year ago

ok, so, if i want to get company name(cname) from table i try:

mocart Over a year ago

ok, so, if i want to get company name(cname) from table i try: CREATE FUNCTION getByCompId (cid INT) RETURN (SELECT cname FROM companies WHERE id=cid); and have error right syntax to use near 'RETURN

Collectives™ on Stack Overflow

You have an error in your SQL syntax (CREATE PROCEDURE)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related