Incorrect syntax near the keyword 'CREATE' while creating function

sql-server

Solution

You should also end the each of create statements with a `GO` or semicolon:

Also, should add the schema to the function. For example the below uses the `dbo.` schema:

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 of all functions being created.

Problem

I am a complete newbie when it comes to MS SQL and have found this code online while searching. It seems like it would do exactly what I want, which is do a radius search based on Latitude and Latitude values. However, I keep getting: Incorrect syntax near the keyword 'CREATE'. , which is the very first line of the code. My database is 2008 MS SQL 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's going on? Thank you!!! EDIT: Thank you very much to bluefeet and Aaron Bertrand for pointing me in the right direction!

Original source