CODEWITHSUNDEEP
mysqlfunctionphpmyadmin
skiwi
skiwi

Reputation: 69269

MySQL simple function not working on variable declaration

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.

Upvotes: 1

Views: 4130

Answers (2)

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

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.

Upvotes: 3

skiwi
skiwi

Reputation: 69269

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.

Upvotes: 0

Related Questions