MySQL simple function not working on variable declaration

Question

I am trying to define my own FUNCTION on MySQL inside phpMyAdmin:

BEGIN
DECLARE output VARCHAR
DECLARE temp DATETIME
SET temp = DATEDIFF(NOW(), added)
CASE temp
WHEN 0 SET output = 'today'
WHEN 1 SET output = 'yesterday'
ELSE SET output = CONCAT(temp, ' days ago')
RETURN output
END

The error is the following:

De volgende query is mislukt: "CREATE FUNCTION DAYSPASSED(date DATETIME) RETURNS VARCHAR(255) NOT DETERMINISTIC MODIFIES SQL DATA SQL SECURITY DEFINER BEGIN DECLARE output VARCHAR DECLARE temp DATETIME SET temp = DATEDIFF(NOW(), added) CASE temp WHEN 0 SET output = 'today' WHEN 1 SET output = 'yesterday' ELSE SET output = CONCAT(temp, ' days ago') RETURN output END"

MySQL retourneerde: #1064 - 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 'DECLARE temp DATETIME SET temp = DATEDIFF(NOW(), added) CASE temp WHEN 0 SET ' at line 3

However I cannot quite see what is wrong with it.

@Barmar Doesn't seem to change anything and it gives error on DELIMITER $$ before BEGIN. And I thought that phpMyAdmin should handle that. — skiwi
– skiwi, Commented Dec 26, 2013 at 18:12

Joachim Isaksson · Accepted Answer · 2013-12-26 18:27:35Z

3

You're missing some semicolons, a length specifier for your varchar, and there are some errors in your case syntax. This would seem to work with a // delimiter;

DELIMITER //
CREATE FUNCTION DAYSPASSED(added DATETIME) RETURNS VARCHAR(255) 
NOT DETERMINISTIC 
MODIFIES SQL DATA 
SQL SECURITY DEFINER 
BEGIN 
  DECLARE output VARCHAR(32);
  DECLARE temp INT;
  SET temp = DATEDIFF(NOW(), added);
  CASE temp WHEN 0 THEN SET output = 'today'; 
            WHEN 1 THEN SET output = 'yesterday';
            ELSE SET output = CONCAT(temp, ' days ago');
  END CASE;
  RETURN output;
END//
DELIMITER ;

An SQLfiddle to test with.

edited Dec 26, 2013 at 18:27

answered Dec 26, 2013 at 18:19

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

skiwi · Accepted Answer · 2013-12-26 18:19:12Z

0

The answer needs to be the following:

BEGIN
DECLARE output VARCHAR(255) DEFAULT '';
DECLARE temp INT;
SET temp = DATEDIFF(NOW(), date);
CASE temp
WHEN 0 THEN SET output = 'today';
WHEN 1 THEN SET output = 'yesterday';
ELSE SET output = CONCAT(temp, ' days ago');
END CASE;
RETURN output;
END

Note the following changes:

Lines need to be terminated by a semicolon ;
output needs to have a DEFAULT '' value.
The syntax for a CASE is CASE ... WHEN ... THEN ... END CASE

Edit: temp should be an INT.

answered Dec 26, 2013 at 18:19

skiwi

69.8k32 gold badges140 silver badges225 bronze badges

Collectives™ on Stack Overflow

MySQL simple function not working on variable declaration

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