CODEWITHSUNDEEP
mysqlsqlstored-procedures
Luis D Urraca
Luis D Urraca

Reputation: 2084

MySQL stored procedure, if Resultset from stored procedure is empty then change one variable and run again

I have an stored procedure:

DROP PROCEDURE `getCercanoRadio`//
CREATE DEFINER=`prog2sc`@`localhost` 
PROCEDURE `getCercanoRadio`
  (IN latitude Double, IN longitude Double, IN tipo_servicio INT)
BEGIN
    SET @LAT = latitude;
    SET @LON = longitude;
    SET @Servicio = tipo_servicio;
    SET @point = CONCAT('POINT(',@LAT,' ',@LON,')');
    SET @center = GeomFromText(@point); 
    SET @radius = 0.01; 
    SET @bbox = CONCAT('POLYGON((', 
    X(@center) - @radius, ' ', Y(@center) - @radius, ',', 
    X(@center) + @radius, ' ', Y(@center) - @radius, ',', 
    X(@center) + @radius, ' ', Y(@center) + @radius, ',', 
    X(@center) - @radius, ' ', Y(@center) + @radius, ',', 
    X(@center) - @radius, ' ', Y(@center) - @radius, '))'); 

    SELECT
      prog2sc_SCDBs.Punto_Geografico.latitude
      ,prog2sc_SCDBs.Punto_Geografico.longitude
      , prog2sc_SCDBs.Tipo_Servicio.idTipo_Servicio
      , prog2sc_SCDBs.Ubicacion.nombreUbicacion
      , SQRT(POW( ABS( X(geopoint) - X(@center)), 2) 
        + POW( ABS(Y(geopoint) - Y(@center)), 2 )) AS distance 
    FROM prog2sc_SCDBs.Ubicacion 
    INNER JOIN prog2sc_SCDBs.Tipo_Servicio ON
      prog2sc_SCDBs.Ubicacion.Tipo_Servicio_idTipo_Servicio = idTipo_Servicio
    INNER JOIN prog2sc_SCDBs.Punto_Geografico ON 
      prog2sc_SCDBs.Ubicacion.idUbicacion =  
        prog2sc_SCDBs.Punto_Geografico.idPunto_Geografico
    WHERE Intersects( geopoint, GeomFromText(@bbox) )
    AND idTipo_Servicio=@Servicio
    AND (
      SQRT(POW( ABS( X(geopoint) - X(@center)), 2) 
      + POW( ABS(Y(geopoint) - Y(@center)), 2 ))
         ) < @radius 
    ORDER BY distance,geopoint;
  END

I want to make a condition check, if resultset from this store prodedure is empty, then increase @radius and run again until resulset is not null.

Upvotes: 0

Views: 1844

Answers (1)

Simon
Simon

Reputation: 2008

In order to keep running the statement until you get a certain result, you'll need a LOOP statement. Also, Instead of just running a SELECT, you should add an INTO clause to the statement (example here) to temporarily store the result of the query. You'll need to define the variables first - in your case, the variables might be @latitude, @longitude etc. and their definitions should correspond to the data types in the columns.

Once you've run your SELECT ... INTO ..., you can test the contents of the variables, and if it's not what you want, increment your @radius and allow the loop to continue, otherwise, issue a LEAVE to end the loop.

To return the data from your procedure, you can either use OUT parameters (described on the CREATE PROCEDURE manpage), or you can issue another SELECT.

OUT params would mean changing the parameters to your procedure (adding the OUT type parameters to your list of IN params), and the way it's called, asOUT parameters are handled differently than the result of a SELECT within the procedure.

Using the SELECT method, you issue a SELECT for the variables you defined at the end of the procedure. Using this method, the data comes out in the format you have it coming out at the moment. You'd do something like this after your LOOP:

SELECT @latitude, @longitude, ... ;

That method would mean that existing code that calls the procedure wouldn't need to be changed - as long as you alias the variables to match the column names of the existing query.

Upvotes: 2

Related Questions