Dean Christian Armada Dean Christian Armada - 4 months ago 7
MySQL Question

MySQL Creating a Function with conditional

I am trying to create a mysql function with a condition that I certain word must be in the parameter for it to work

CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
DETERMINISTIC
IF s = NULL
THEN RETURN CONCAT('Hello World!')


So if the query is

SELECT hello(NULL);


Then it should output:

+--------------------+
| hello(NULL) |
+--------------------+
| Hello Hello World! |
+--------------------+


Otherwise there shouldn't be any rows returned

When I try to do my code above, it always return a syntax error. The thing is I 'm not really good at creating mysql functions especially if there are conditionals

PS

I tried this code but I got the following error

mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
-> DETERMINISTIC
-> IF s IS NULL
-> THEN RETURN CONCAT('Hello World!');
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 '' at line 4

JPG JPG
Answer

Your function has several syntax errors:

 DELIMITER &&

 CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50) 
 DETERMINISTIC 
 BEGIN
     IF s IS NULL THEN
         RETURN CONCAT('Hello World!');
     END IF;
 END&&

 DELIMITER ;
  • Try to define a delimiter
  • Use begin, end in function body
  • You need end if for if clause
  • = null should be is null