MySql stored procedure, how to insert more than one variable with CONCAT

Question

Basically I have the following stored procedure:

BEGIN
SET @query := CONCAT("SELECT *,
sqrt( 
        (POW(a.Latitude - co.CenterLatitude, 2)* 68.1 * 68.1) + 
        (POW(a.Longitude - co.CenterLongitude, 2) * 53.1 * 53.1) 
 ) AS distance
FROM table1 as r
JOIN table2 as co ON co.field1 = r.field2
JOIN table3 AS a ON r.field1 = a.field2
WHERE ",rid);

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END

It has the following passing through:

IN rid varchar(500), lat double, lon double

But I need to pass in the latitude and longitude variables. I have tried setting them then adding them into the query but it is not recognizing them. This is what I am trying to do which is not successful:

    BEGIN
    SET @lat := lat;
    SET @lon := lon;
    SET @query := CONCAT("SELECT *,
    sqrt( 
            (POW(a.Latitude - @lat, 2)* 68.1 * 68.1) + 
            (POW(a.Longitude - @lon, 2) * 53.1 * 53.1) 
     ) AS distance
    FROM table1 as r
    JOIN table2 as co ON co.field1 = r.field2
    JOIN table3 AS a ON r.field1 = a.field2
    WHERE ",rid);

    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    END

I am not sure how to accomplish this. Does anyone have any suggestions? Thank you in advance!!

Couldn't you just include them in the concatenation as you did rid? — Uueerdo
– Uueerdo, Commented May 16, 2016 at 18:13

Barmar · Accepted Answer · 2016-05-16 18:13:41Z

3

You can put ? placeholders in the prepared statement, and then supply the values when executing it.

SET @query := CONCAT("SELECT *,
sqrt( 
        (POW(a.Latitude - ?, 2)* 68.1 * 68.1) + 
        (POW(a.Longitude - ?, 2) * 53.1 * 53.1) 
 ) AS distance
FROM table1 as r
JOIN table2 as co ON co.field1 = r.field2
JOIN table3 AS a ON r.field1 = a.field2
WHERE ",rid);
PREPARE stmt FROM @query;
EXECUTE stmt USING @lat, @lon;

answered May 16, 2016 at 18:13

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

3 Comments

lov2code Over a year ago

I just tried this and the response time is about 8 times what it was before, do you have any idea why that might be?

lov2code Over a year ago

Thank you for answering by the way!

lov2code Over a year ago

Figured it out, thank you so much!! Works like a charm :)

Uueerdo · Accepted Answer · 2016-05-16 18:37:47Z

1

Barmar's answer would be my preferred solution, but to give an example of my what my initial comment was suggesting...

BEGIN
SET @query := CONCAT("SELECT *,
sqrt( 
        (POW(a.Latitude - ", lat, ", 2)* 68.1 * 68.1) + 
        (POW(a.Longitude - ", lon, ", 2) * 53.1 * 53.1) 
 ) AS distance
FROM table1 as r
JOIN table2 as co ON co.field1 = r.field2
JOIN table3 AS a ON r.field1 = a.field2
WHERE ",rid);

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END

answered May 16, 2016 at 18:37

Uueerdo

16k1 gold badge19 silver badges21 bronze badges

Collectives™ on Stack Overflow

MySql stored procedure, how to insert more than one variable with CONCAT

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related