mysql function if/else

Question

I have a function that returns a date string. I need this because I can't use a variable in a view, but I can use a function that returns a variable that I set ahead of time...

So I got all that working, but then I decided that if that I wanted it to return the current date if no date variable was set. I thought the code below wold work, but I get syntax errors...

DELIMITER $$

USE `cc`$$

DROP FUNCTION IF EXISTS `ox_date`$$

CREATE  FUNCTION `ox_date`() RETURNS CHAR(50) CHARSET latin1
DECLARE ox VARCHAR(20)
IF @oxdate <1 THEN SET ox = CURDATE$$
ELSE SET ox = @oxdate$$

RETURN ox $$

DELIMITER ;

I tried isnull on that first if, but it wasn't any help.

Answer 1

I'm no expert but here are a few of things I see.

First, you've got

DELIMITER $$

and then use it in the function itself. That DELIMITER line allows you to use the semicolons within the body of the function. Otherwise the ';' would end the CREATE FUNCTION statement prematurely.

Also, the line

DECLARE ox varchar(20)

is missing a semicolon at the end.

And then you're missing the

END IF;

after the else condition.

Also what about the BEGIN END$$ wrapped around the function's definition?

I'd expect a stored function to generally take the form:

DELIMITER $$
DROP FUNCTION IF EXISTS `testdb`.MyFunc$$
CREATE FUNCTION `testdb`.`MyFunc` () RETURNS INT
BEGIN
   DECLARE someVar varchar(20);
   # some stuff
   RETURN something;
END $$

DELIMITER ;

Modifying the guts of the function to suit your needs and setting the return type as appropriate.

Anyway, I'm not an expert but that is what I see and hope that helps.

Answer 2

Why do you need to fiddle with the delimiter? For simple logic prefer the IF function to the IF statement.

CREATE FUNCTION `ox_date`( )
RETURNS CHAR(50) CHARSET latin1
RETURN IF(@oxdate < 1 OR @oxdate IS NULL, CURDATE(),@oxdate)

Else

DELIMITER $$

USE `cc`$$

DROP FUNCTION IF EXISTS `ox_date`$$

CREATE FUNCTION `ox_date`( )
RETURNS CHAR(50) CHARSET latin1
RETURN IF(@oxdate < 1 OR @oxdate IS NULL, CURDATE(),@oxdate)$$

DELIMITER ;