-1

Hello everyone i am trying to create an advanced search for restaurants with many filters for the user to select. One of the them is going to be the distance between his/her home and the restaurants. So i want to use a function in sql where it gets the two latitudes and two longitudes and checks if the distance is smaller than the filter.

I' ve found some solutions in here such as Calculating distance between two points (Latitude, Longitude) but i just cant create the function no matter i do. I always get the same 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". I even try to create a more simple function such as How to create a function in SQL Server but i still get the same error. I simply copy the function and paste it in my server to run a sql query on database.
Is there something that i m missing?? (maybe the create function is not supported anymore?).
Thanks in advance for any suggestions/answers.

Community
  • 1
  • 1
Bro Code
  • 1
  • 2
  • 1
    Links you've shown are about MS SQL Server. But according to error message you've show - you're using MySQL. Why do you suppose the syntax to be identical? – Andrey Korneyev Mar 14 '17 at 08:12
  • these are some examples that i ve tried. I just wanted to create a function to figure out how it works – Bro Code Mar 14 '17 at 08:30
  • You're using MySQL. This DBMS syntax is generally different from MS SQL syntax. So if you want to create function in MySQL - it is better to look into *relevant* examples, say https://dev.mysql.com/doc/refman/5.7/en/create-procedure.html – Andrey Korneyev Mar 14 '17 at 08:33

1 Answers1

0

as per your tagging as sql-server, you should be able to create function like below to return the distance in SQL Server.

CREATE FUNCTION fnGetDistance(@source geography, @target geography)
RETURNS FLOAT
AS 
BEGIN
   RETURN @source.STDistance(@target)
END

Then you can call the Function to get the distance between two points.

SELECT [dbo].[fnGetDistance] ('POINT(0 51.5)', 'POINT(-3 56)')
Venu
  • 455
  • 2
  • 7
  • Error text provided in question clearly shows that OP uses MySQL and `sql-server` tag he used is misleading. – Andrey Korneyev Mar 14 '17 at 08:26
  • yes @AndyKorneyev you are right, it probably is misleading. I just didnt see that locally said Mysql because at my online server it says just a name "MariaDB server version" – Bro Code Mar 14 '17 at 08:35