MySql 5 Functions

BlueSpud

I am tearing out what is left of my hair on this problem.

I am using MySql Server version : 5.0.45-community-nt and I am attempting to write a stored function. I have tried a clatter of examples from the web but none of them will work for me. Can anyone suggest what I might be doing wrong?

DELIMITER ;
CREATE FUNCTION test ( input_int int )
RETURNS int
BEGIN
DECLARE var_returned int;
IF input_int > 20 THEN
SET var_returned = 50;
END IF;
return var_returned;
END;

I get the following message

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.

Ultimatly I want a function that will take a string and return a blank string if the parameter id null. e.g.

DELIMITER ;
CREATE FUNCTION convertNull ( input_str VARCHAR(200) )
RETURNS VARCHAR(200)
BEGIN
   DECLARE result_str VARCHAR(200);
   SET result_str  = input_str;
   IF isNull(result_str)  THEN
      SET result_str  = "";
   END IF;
   return result_str;
END;

lynchie

Does the following work?

DELIMITER $$
CREATE FUNCTION test ( input_int int )
RETURNS int
BEGIN
DECLARE var_returned int;
IF input_int > 20 THEN
SET var_returned = 50;
END IF;
return var_returned;
END$$

BlueSpud

No joy, it gives the following error:

[SIZE=1]Error Code : 1064[/SIZE]
[SIZE=1]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 'IF input_int > 20 THEN[/SIZE]
[SIZE=1]SET var_returned = 50;[/SIZE]
[SIZE=1]END IF;[/SIZE]
[SIZE=1]return var_returned;[/SIZE]
[SIZE=1]END$$' at line 1[/SIZE]
[SIZE=1](0 ms taken)[/SIZE]
 

lynchie

Strange, it works fine for me on mysql 5.0.67

mysql> drop function test;
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER $$
mysql> CREATE FUNCTION test ( input_int int )
    -> RETURNS int
    -> BEGIN
    -> DECLARE var_returned int;
    -> IF input_int > 20 THEN
    -> SET var_returned = 50;
    -> END IF;
    -> return var_returned;
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql> exit

BlueSpud

You are using the command prompt, however, I am using SQLyog as a front end, as I will be running the scripts against a number of databases.