MySQL stored function with nested IF... END IF, error in syntax, right syntax to use near ''

Question

I have a function that I currently use in PHP which compiles a mailing address from separate fields but takes into account different formats used in different regions. I'm trying to replicate this as a MySQL stored function. I realise that it's often faster to do this sort of thing in code rather than in the database but our intranet has a way for people to enter raw MySQL SELECT commands in read-only so they can construct advanced searches and save the queries. This particular function will be used so that users can output their advanced search query results to a label layout.

When I try and store the function using phpMyAdmin 3.4.9 (latest stable) I get the following error:

#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 '' at line 51

I also installed the latest MySQL Workbench and get the same error but also it's highlighted an "SQL syntax error near 'END'" so it's not just a bug in phpMyAdmin (though it could be a bug in both phpMyAdmin and MySQL Workbench).

Here's the function query:

DELIMITER ;;
DROP FUNCTION IF EXISTS ADDRESS_BUILD;;
CREATE FUNCTION ADDRESS_BUILD(contact VARCHAR(50), company VARCHAR(100), add1 VARCHAR(255), add2 VARCHAR(255), add3 VARCHAR(255), town_city VARCHAR(50), county_state VARCHAR(50), postcode_zip VARCHAR(50), country VARCHAR(100), `separator` VARCHAR(10), type VARCHAR(10))
RETURNS TEXT
DETERMINISTIC
BEGIN
DECLARE address TEXT;
DECLARE line TEXT;

IF LENGTH(TRIM(contact))>0 THEN SET address=CONCAT_WS(`separator`, address, TRIM(contact)); END IF;
IF LENGTH(TRIM(company))>0 THEN SET address=CONCAT_WS(`separator`, address, TRIM(company)); END IF;
IF LENGTH(TRIM(add1))>0 THEN SET address=CONCAT_WS(`separator`, address, TRIM(add1)); END IF;
IF LENGTH(TRIM(add2))>0 THEN SET address=CONCAT_WS(`separator`, address, TRIM(add2)); END IF;
IF LENGTH(TRIM(add3))>0 THEN SET address=CONCAT_WS(`separator`, address, TRIM(add3)); END IF;

IF country='United States of America' OR country='USA' OR country='Canada' OR country='CA' THEN 
    /* NORTH AMERICA, ALL ON 1 LINE */
    IF LENGTH(TRIM(town_city))>0 THEN 
        IF type='mail' THEN SET line=CONCAT_WS('', TRIM(town_city), ' ');
        ELSE SET line=CONCAT_WS('', line, TRIM(town_city), ', ');
        END IF;
    END IF;

    IF LENGTH(TRIM(county_state))>0 THEN 
        IF type='mail' THEN SET line=CONCAT_WS('', line, TRIM(county_state), '  ');
        ELSE SET line=CONCAT_WS('', line, TRIM(county_state), ' ');
        END IF;
    END IF;

    IF LENGTH(TRIM(postcode_zip))>0 THEN SET line=CONCAT_WS('', line, TRIM(postcode_zip)); END IF;

    SET address=CONCAT_WS(`separator`, address, TRIM(line));

ELSE IF country='United Kingdom' OR country='UK' THEN 
    /* UK, ASCENDING LOCALITY SEPARATE LINES */
    IF LENGTH(TRIM(town_city))>0 THEN SET address=CONCAT_WS(`separator`, address, TRIM(town_city)); END IF;
    IF LENGTH(TRIM(county_state))>0 THEN SET address=CONCAT_WS(`separator`, address, TRIM(county_state)); END IF;
    IF LENGTH(TRIM(postcode_zip))>0 THEN SET address=CONCAT_WS(`separator`, address, TRIM(postcode_zip)); END IF;

ELSE
    /* EUROPE EVERYWHERE ELSE, ALL ON 1 LINE POSTCODE FIRST */
    IF LENGTH(TRIM(postcode_zip))>0 THEN SET line=CONCAT_WS('', line, TRIM(postcode_zip)); END IF;
    IF LENGTH(TRIM(town_city))>0 THEN SET line=CONCAT_WS('', line, ' ', TRIM(town_city)); END IF;
    IF LENGTH(TRIM(county_state))>0 THEN SET line=CONCAT_WS('', line, ' ', TRIM(county_state)); END IF;
    IF LENGTH(TRIM(line))>0 THEN SET address=CONCAT_WS(`separator`, address, TRIM(line)); END IF;
END IF;

IF country='United States of America' THEN SET address=CONCAT_WS(`separator`, address, 'USA');
ELSE IF LENGTH(TRIM(country))>0 THEN SET address=CONCAT_WS(`separator`, address, TRIM(country));
END IF;

RETURN address;
END;;

Line 51 is near the END IF, RETURN and END clauses but I can't spot anything wrong with that.

Can anyone see what's causing this problem in both MySQL Workbench and phpMyAdmin?
Once I've got the function stored, then I can test it out and tweak the logic.

Also if there's any stuff in the function that could be streamlined then let me know. There's not many examples out there so I've patched this together somewhat.

Answer 1

Ok I'm going to answer my own question. Thanks to Yahia I took another look at my IF statement syntax.
Turns out I screwed up the query!

I've got 2 ELSE IF clauses above.
The proper syntax according to http://dev.mysql.com/doc/refman/5.0/en/if.html is actually ELSEIF
I just assumed that because of END IF that it should also be ELSE IF with a space, without actually making sure from the docs.

So MySQL was quite correctly interpreting the ELSE IF as an ELSE then the start of another nested IF, instead of another branch of the same level.

The above query works perfectly in phpMyAdmin once you correct the ELSE IFs but I've got a few tweaks to the logic to make.

So all my fault and RTM!

Answer 2

Other solution. Try with a function:

DROP FUNCTION IF EXISTS test;

DELIMITER $$
CREATE FUNCTION test(name VARCHAR(255), id INT) RETURNS INT
BEGIN
    DECLARE var_resp INT DEFAULT 0;

    IF (LENGTH(TRIM(name)) > 0) THEN
        UPDATE mytableset IDName= name WHERE mytable.ID = id
        SET var_resp = 1;
    END IF;

    RETURN var_resp;

END $$
DELIMITER ;

Regards.

Answer 3

you are missing an END IF; - change the last lines to:

IF country='United States of America' THEN SET address=CONCAT_WS(`separator`, address, 'USA');
ELSE IF LENGTH(TRIM(country))>0 THEN SET address=CONCAT_WS(`separator`, address, TRIM(country));
END IF;
END IF;

RETURN address;
END;;