Geek Answers Handbook

Incorrect syntax next to the keyword "CREATE" when creating a function

I am a complete newbie when it comes to MS SQL and found this code online while searching. It looks like this will do exactly what I want, it is a radius search based on the Latitude and Latitude values.

However, I keep getting: Invalid syntax next to the keyword "CREATE". , which is the very first line of code. My database is MS SQL 2008

Here is the code:

CREATE FUNCTION CalculateDistance (@Longitude1 Decimal(8,5), @Latitude1 Decimal(8,5), @Longitude2 Decimal(8,5), @Latitude2 Decimal(8,5)) Returns Float AS BEGIN Declare @Temp Float Set @Temp = sin(@Latitude1/57.2957795130823) * sin(@Latitude2/57.2957795130823) + cos(@Latitude1/57.2957795130823) * cos(@Latitude2/57.2957795130823) * cos(@Longitude2/57.2957795130823 - @Longitude1/57.2957795130823) if @Temp > 1 Set @Temp = 1 Else If @Temp < -1 Set @Temp = -1 Return (3958.75586574 * acos(@Temp) ) End -- FUNCTION CREATE FUNCTION LatitudePlusDistance(@StartLatitude Float, @Distance Float) Returns Float AS BEGIN Return (Select @StartLatitude + Sqrt(@Distance * @Distance / 4766.8999155991)) End -- FUNCTION CREATE FUNCTION LongitudePlusDistance (@StartLongitude Float, @StartLatitude Float, @Distance Float) Returns Float AS BEGIN Return (Select @StartLongitude + Sqrt(@Distance * @Distance / (4784.39411916406 * Cos(2 * @StartLatitude / 114.591559026165) * Cos(2 * @StartLatitude / 114.591559026165)))) End -- ACTUAL QUERY -- Declare some variables that we will need. Declare @Longitude Decimal(8,5), @Latitude Decimal(8,5), @MinLongitude Decimal(8,5), @MaxLongitude Decimal(8,5), @MinLatitude Decimal(8,5), @MaxLatitude Decimal(8,5) -- Get the lat/long for the given id Select @Longitude = Longitude, @Latitude = Latitude From qccities Where id = '21' -- Calculate the Max Lat/Long Select @MaxLongitude = LongitudePlusDistance(@Longitude, @Latitude, 20), @MaxLatitude = LatitudePlusDistance(@Latitude, 20) -- Calculate the min lat/long Select @MinLatitude = 2 * @Latitude - @MaxLatitude, @MinLongitude = 2 * @Longitude - @MaxLongitude -- The query to return all ids within a certain distance Select id From qccities Where Longitude Between @MinLongitude And @MaxLongitude And Latitude Between @MinLatitude And @MaxLatitude And CalculateDistance(@Longitude, @Latitude, Longitude, Latitude) <= 2

Any idea what is going on?

Thanks!!!

EDIT: Many thanks to Sinethe and Aaron Bertrand for pointing me in the right direction!

+4
source share
4 answers

You must also end each create statement with GO or a semicolon:

In addition, you should add the circuit to the function. For example, the dbo. scheme is used below dbo. :

 CREATE FUNCTION dbo.CalculateDistance (@Longitude1 Decimal(8,5), @Latitude1 Decimal(8,5), @Longitude2 Decimal(8,5), @Latitude2 Decimal(8,5)) Returns Float AS BEGIN Declare @Temp Float Set @Temp = sin(@Latitude1/57.2957795130823) * sin(@Latitude2/57.2957795130823) + cos(@Latitude1/57.2957795130823) * cos(@Latitude2/57.2957795130823) * cos(@Longitude2/57.2957795130823 - @Longitude1/57.2957795130823) if @Temp > 1 Set @Temp = 1 Else If @Temp < -1 Set @Temp = -1 Return (3958.75586574 * acos(@Temp) ) End GO

See SQL Fiddle with Demo for all created functions.

+2
source

Try to perform one function without GO . GO not part of T-SQL; it is a batch separator for client tools such as Management Studio. ColdFusion probably passes this along with SQL Server and does not understand GO . Therefore, either send one function at a time from ColdFusion (without GO ), or use the best tool for creating objects and sending requests (for example, Management Studio or any other client interface provided by the host).

I'm not sure why you think you need to create three functions in one block of code instead of creating them separately (since your web page obviously has no idea about batches). You only need to create functions once. After you have created them in the database, you can refer to them in subsequent queries all day / week / month / year, etc.

+2
source

You need to separate multiple CREATE FUNCTION calls with GO OR ; (or both - which one do I prefer):

 CREATE FUNCTION CalculateDistance (@Longitude1 Decimal(8,5), @Latitude1 Decimal(8,5), @Longitude2 Decimal(8,5), @Latitude2 Decimal(8,5)) Returns Float AS BEGIN Declare @Temp Float Set @Temp = sin(@Latitude1/57.2957795130823) * sin(@Latitude2/57.2957795130823) + cos(@Latitude1/57.2957795130823) * cos(@Latitude2/57.2957795130823) * cos(@Longitude2/57.2957795130823 - @Longitude1/57.2957795130823) if @Temp > 1 Set @Temp = 1 Else If @Temp < -1 Set @Temp = -1 Return (3958.75586574 * acos(@Temp) ) End; GO -- FUNCTION CREATE FUNCTION LatitudePlusDistance(@StartLatitude Float, @Distance Float) Returns Float AS BEGIN Return (Select @StartLatitude + Sqrt(@Distance * @Distance / 4766.8999155991)) End -- FUNCTION CREATE FUNCTION LongitudePlusDistance (@StartLongitude Float, @StartLatitude Float, @Distance Float) Returns Float AS BEGIN Return (Select @StartLongitude + Sqrt(@Distance * @Distance / (4784.39411916406 * Cos(2 * @StartLatitude / 114.591559026165) * Cos(2 * @StartLatitude / 114.591559026165)))) End; GO
0
source

This SQL contains several CREATE statements. They should be divided into lots, putting GO between statements.

This is revealed by half the message in SSMS:

'CREATE FUNCTION' must be the first statement in the query package.

So, to fix:

 CREATE FUNCTION CalculateDistance (@Longitude1 Decimal(8,5), @Latitude1 Decimal(8,5), @Longitude2 Decimal(8,5), @Latitude2 Decimal(8,5)) Returns Float AS BEGIN ... END GO CREATE FUNCTION LatitudePlusDistance(@StartLatitude Float, @Distance Float) Returns Float AS BEGIN Return (Select @StartLatitude + Sqrt(@Distance * @Distance / 4766.8999155991)) End GO CREATE FUNCTION... etc
0
source

More articles:


All Articles