Calling a Function to Update Temp Table

Question

Calling a Function to Update Temp Table

SSCrazy

Points: 2784

March 8, 2021 at 4:04 pm

Hello, I have created a function that will determine the Distance between 2 sets of Lat and Log Coordinates. The function is working as desired. As a next step, what I would like to do next is record the difference in miles into my temp table.

I am attempting is to call the Function within a Cursor with the intent to update each row in my temp table, however, what is currently happening is that all records are updating based on the first results, therefore my cursor is not looping correctly.

 DECLARE
    @RID                    int,
    @Latitude1              float,
    @Longitude1             float, 
    @Latitude2              float,
    @Longitude2             float

    DECLARE db_cursor CURSOR FOR
    SELECT RID, Latitude2, Longitude1, Latitude2, Longitude2 FROM #r

    OPEN db_cursor
    FETCH NEXT FROM db_cursor INTO @RID, @Latitude1, @Longitude1, @Latitude2, @Longitude2

    WHILE @@FETCH_STATUS = 0  
    BEGIN 

    DECLARE @Ret nvarchar(15);

    EXEC @ret = fn_Distance @Latitude1, @Longitude1, @Latitude2, @Longitude2

    UPDATE r
    SET Distance = @ret
    FROM #r r
    WHERE r.RID = @RID
    PRINT @Ret

    FETCH NEXT FROM db_cursor INTO @RID, @Latitude1, @Longitude1, @Latitude2, @Longitude2

    END 

    CLOSE db_cursor  
    DEALLOCATE db_cursor

Can someone point out what I am doing wrong? And/or is there a more efficient way of doing this instead of using a cursor?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 100942 More actions · Answer 1

You're using a proc not a function. You should use a function. If you'll post the proc code, we can help you rewrite it as a func.

For the proc, to return a value, you need to use an OUTPUT parameter. The return code is a single integer value, not the result of any calc in the proc. Add a return param to the proc, with OUTPUT specified, and then call the proc more like this:

DECLARE @Ret nvarchar(15);

DECLARE @Return_code int;

EXEC @return_code = fn_Distance @Latitude1, @Longitude1, @Latitude2, @Longitude2, @Ret OUTPUT

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

izhar-azati SSC Veteran Points: 207 More actions · Answer 2

Use:

UPDATE #r

SET Distance =

geography::Point( Latitude1, Longitude1, 4326 ).STDistance( geography::Point( Latitude2, Longitude2, 4326 ) ) * 0.000621371;

0.000621371 is meters to miles.

This reply was modified 3 years, 1 month ago by izhar-azati.