CODEWITHSUNDEEP
mysql
Searene
Searene

Reputation: 27554

Syntax error when creating mysql function

mysqlfile.sql:

DROP FUNCTION IF EXISTS func1;
DELIMITER //
CREATE FUNCTION func1(N INT) RETURNS INT
BEGIN
  RETURN (
      select * from Employee;
  );
END//
DELIMITER ;

executed source mysqlfile.sql, got an error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';
  );
END' at line 4

How should I modify the code?

Upvotes: 0

Views: 43

Answers (4)

Danyal Sandeelo
Danyal Sandeelo

Reputation: 12391

A function returns 1 value, as far I can see you want to return a dataset. You must create procedure in order to get what you need. 

 DELIMITER //
 CREATE PROCEDURE getEmployees()
 BEGIN
  SELECT *  FROM employee;
 END //
 DELIMITER ;

You can call it by call getEmployees();

Upvotes: 1

Ritobroto Mukherjee
Ritobroto Mukherjee

Reputation: 111

TRY:

DROP FUNCTION IF EXISTS func1;
CREATE FUNCTION func1(N INT) RETURNS INT
BEGIN
DECLARE select_var VARCHAR;
SET select_var = (SELECT * FROM Employee);
RETURN VARCHAR(255);
END$$

Hope it'll solve your issue

Upvotes: 0

Akshay
Akshay

Reputation: 71

You have missed 'AS' before BEGIN

Upvotes: 0

AdrianBR
AdrianBR

Reputation: 2588

you missed a semicolon after END and have an extra after the select

DROP FUNCTION IF EXISTS func1;
DELIMITER //
CREATE FUNCTION func1(N INT) RETURNS INT
BEGIN
  RETURN (
      select * from Employee
  );
END;//
DELIMITER ;

Upvotes: 1

Related Questions