Unable to use declare statement in CREATE FUNCTION

Question

I am trying to use a declare [variable] statement within a CREATE FUNCTION statement.

My code is

/* DELIMITER // */

CREATE FUNCTION hello_world()
  RETURNS TEXT
DECLARE bae int;
BEGIN
  RETURN 'Hello World';
END;
//
/* DELIMITER ; */

The code just worked fine without using declare, but with the declare it gives me the error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use...

How can I actually use the declare statement inside function or stored procedure?

Answer 1

Don't comment out the delimiter, and declares need to be immediately after the begin statement:

DELIMITER // 
CREATE FUNCTION hello_world() RETURNS TEXT
BEGIN
DECLARE bae INT;
  RETURN 'Hello World';
END
//
DELIMITER; --restore delimiter

SELECT hello_world() --Hello World

EDIT:

I've never used sql fiddle. I've played with it for 5 minutes and I hate it :)

Apparantly, sql fiddle has different syntax rules. The one that applies here is that they do not support delimiters, but do have // built in as a "known delimiter" (Execute triggers stored procedures on SqlFiddle. Mysql)

So, this works:

CREATE function hello_world() returns text
BEGIN
declare bae int;
return "hello world";
END//

select hello_world()

http://sqlfiddle.com/#!2/8e5da4/1

So, there is a working fiddle. I hand-typed the function and it works. HOWEVER, I literally copy pasted that into a new sqlfiddle and I get

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 3

So this inconsistant behavior is pretty annoying.