6

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!

Improve this question
10
  • In addition to separating your batches using GO, please use the dbo. prefix when creating/referencing objects, especially functions. Commented Apr 2, 2013 at 17:05
  • @AaronBertrand hello Aaron, my server is on a shared environment, and the MS SQL database tables are all not inside of dbo., instead they are something like user21587. So my table would be user21587.qccities does that make a difference? I did try both my prefix and the dbo. prefix, and both are still giving me errors, now it says: Incorrect syntax near 'go'. Commented Apr 2, 2013 at 19:47
  • where exactly are you running this code? Have you tried just running each CREATE FUNCTION separately? Commented Apr 2, 2013 at 19:51
  • I am running it inside of a coldfusion page. And yes I've tried stripping everything except one function. I've done them one at a time, and all are giving me Incorrect syntax near 'go'. Something else must be going on since the SQL Fiddle below provided by bluefeet works just fine. Do you think it could have something to do with my database being on a shared environment? Do functions need special permissions to run or something? But then again, I'm not getting any weird permission errors...I'm getting an error of incorrect syntax :( Commented Apr 2, 2013 at 20:11
  • Did you 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). Commented Apr 2, 2013 at 20:18

4 Answers 4

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

13 Comments

@JonEgerton You are right, I was updating my answer to include the missing GO and to suggest using dbo. or some schema.
@bluefeet thank you for your reponse. And I see that the sql Fiddle is working, however, when I add that same code into my page I now get: Incorrect syntax near 'go' Am i doing something wrong by having the functions and the actual query in the same sql block? Or? Maybe I am not 100% clear on how functions work :(
@Jennifer The GO is used to separate each batch and the creation of the function needs to be in separate batches. If you take your queries and run those in a separate script or query window do they work after the functions are created?
@Jennifer you say "into my page" - what page are you talking about?
@bluefeet Well no, even if I remove EVERYTHING from my query window, except the very first create function, until the ending "go" I still get incorrect syntax near 'go'. I then tried just leaving the function batches in the query call and it also gives me the same error. In other words, I basically copied your SQL fiddle and just pasted that in, w/o my actual query that uses the functions, and I still get the error. Should a function be able to stand on its own? Without anything calling it later in the code?
|
2

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.

Improve this answer

Comments

0

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
Improve this answer

Comments

0

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
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.