CODEWITHSUNDEEP
mysqlmariadb
mocart
mocart

Reputation: 615

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

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

Upvotes: 0

Views: 216

Answers (1)

GMB
GMB

Reputation: 222462

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
;

Upvotes: 1

Related Questions