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.

slightly modified, but still fails... 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$$ END IF$$ RETURN ox $$ DELIMITER ; ............ (0 row(s) affected) Execution Time : 00:00:00:000 Transfer Time : 00:00:00:000 Total Time : 00:00:00:000 (0 row(s) affected, 1 warning(s)) Execution Time : 00:00:00:000 Transfer Time : 00:00:01:061 Total Time : 00:00:01:061 Error Code : 1064 You have an error in your SQL syntax; check — bbutle01
– bbutle01, Commented Oct 13, 2009 at 18:42

itsmatt · Accepted Answer · 2009-10-13 18:50:09Z

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.

martin clayton · Accepted Answer · 2009-10-13 21:50:27Z

0

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 ;

answered Oct 13, 2009 at 21:50

martin clayton

78.6k33 gold badges220 silver badges201 bronze badges

Collectives™ on Stack Overflow

mysql function if/else

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related