Can anyone help me with a MySQL stored procedure

Question

I have never done one before and am trying to work through an online guide but I can't seem to find a good example of one with parameters.

CREATE PROCEDURE `calcdistance` (
    IN ulat varchar, 
    IN ulon varchar, 
    IN clat varchar, 
    IN clon varchar)
BEGIN
Select DEGREES(ACOS(SIN(RADIANS(ulat))
    * SIN(RADIANS(clat))
    + COS(RADIANS(ulat))
    * COS(RADIANS(clat))
    * COS(RADIANS(ulon - ulon))))) * 69.09 AS distance
END

the error MySQL admin gives me when I try this is:

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 '  IN ulat varchar, 
    IN ulon varchar, 
    IN clat varchar, 
    IN cl' at line 2 (error 1064)

Answer 1

In your query, varchar should be varchar(255). And I think that a stored procedure should end in ; by default, unless you use DELIMITER.

Answer 2

you can try

DELIMITER $$
DROP PROCEDURE IF EXISTS calcdistance$$
CREATE PROCEDURE `calcdistance` (IN ulat VARCHAR(50), IN ulon VARCHAR(50), IN clat VARCHAR(50), IN clon VARCHAR(50))
    BEGIN
    SELECT 
    DEGREES(ACOS(SIN(RADIANS(ulat)) 
    * SIN(RADIANS(clat))
    + COS(RADIANS(ulat))
    * COS(RADIANS(clat))
    * COS(RADIANS(ulon - ulon))))
    * 69.09 AS distance;


     END$$

DELIMITER ;