CODEWITHSUNDEEP
sql-server
Jennifer
Jennifer

Reputation: 135

Incorrect syntax near the keyword 'CREATE' while creating function

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!

Upvotes: 6

Views: 12338

Answers (4)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

Try a single function without the GO. GO is not part of T-SQL, it is a batch separator for client tools like Management Studio. ColdFusion is probably passing that along to SQL Server and it doesn't understand the GO. So either send one function at a time from ColdFusion (without the GO), or use a better tool for creating objects and sending queries (e.g. Management Studio or whatever client interface the host provides you with).

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

Upvotes: 2

Taryn
Taryn

Reputation: 247620

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.

Upvotes: 3

Jon Egerton
Jon Egerton

Reputation: 41539

There are a number of CREATE statements in that SQL. They have to be separated into batches by putting GO between the statements.

This is revealed by the half of the message in SSMS:

'CREATE FUNCTION' must be the first statement in a query batch.

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

Upvotes: 0

MarcinJuraszek
MarcinJuraszek

Reputation: 125610

You have to separate multiple CREATE FUNCTION calls with GO OR ; (or both - which one 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

Upvotes: 0

Related Questions