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?

It looks like there is an issue with SQL Fiddle for examples like this. I'm going to try [installing mysql locally](https://dev.mysql.com/downloads/installer/), and I suggest you do the same. — Phil Walton, May 02 '15 at 17:55
a local install is the way to go. phil's method is good, personally I have a linux vm in virtual box, and connect to it using sqlyog (https://www.webyog.com/). sqlyog isn't free, work bench is (https://www.mysql.com/products/workbench/) — chiliNUT, May 02 '15 at 18:04

score 2 · Answer 1 · edited May 23 '17 at 12:22

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.

@PhilWalton it works at the first time when i copy paste and when i just use int in case of varchar it returns error ..seems like there is some problem with sqlfiddle — coding babe, May 02 '15 at 17:46
see updated answer. I had the same problem. It only worked when I hand typed it, broke when I copy pasted. sql fiddle sorta sucks apparently.... — chiliNUT, May 02 '15 at 17:57
@PhilWalton NP. sqlfiddle is a great concept, but I won't ever use it unless glitches like this are ironed out — chiliNUT, May 02 '15 at 18:30

score 0 · Answer 2 · answered May 02 '15 at 17:34

0

In mysql, you preface each declaration with declare, inside the begin-end block:

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

answered May 02 '15 at 17:34

Mureinik

297,002
52
306
350

still the same error ....it would be helpful if you can give me the example as sql fiddle – coding babe May 02 '15 at 17:35

Unable to use declare statement in CREATE FUNCTION

2 Answers2