Creating A Procedure

Question

I'm at a loss, I can't see the reason something as simple as this won't work:

CREATE PROCEDURE test()
BEGIN
    DECLARE var INT;
    SET var = 0;
END

I'm literally just testing this because I can't seem to create anything at all. The error message I get is:

[ERROR in query 1] 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

Line 3 is the DECLARE statement. Any ideas?

Answer 1

This is a common delimiter problem when creating stored procedures in any SQL platform. The default delimiter is ; so when MySQL sees the first ; on Line 3, it parses as the End of the statement. You have to change the DELIMITER to something else and mark the end of the stored procedure with the new DELIMITER.

-- Change DELIMITER TO // instead of ;
DELIMITER //

CREATE PROCEDURE test()
BEGIN
    DECLARE var INT;
    SET var = 0;
END
//
-- Mark the stored procedure as one statement

DELIMITER ;
-- Change delimiter back to ;