DROP PROCEDURE IF EXISTS search_gws;
DELIMITER ?
CREATE PROCEDURE search_gws (IN number char(20))
BEGIN
  DECLARE tmpNum, swap char(20);
  DECLARE count, testsLeft int;
  SET tmpNum = number;
  SET count = 0;
  SET testsLeft = 4;
  myLoop : WHILE (count<1 AND testsLeft>0) DO
    SELECT COUNT(gw.ip_addr) INTO count FROM gw, lcr WHERE tmpNum=lcr.prefix AND lcr.grp_id = gw.grp_id;
    IF count>0 THEN
      SELECT gw.ip_addr, gw.port, gw.uri_scheme, gw.transport, gw.prefix FROM gw, lcr WHERE tmpNum=lcr.prefix AND lcr.grp_id = gw.grp_id ORDER BY CHAR_LENGTH(lcr.prefix), lcr.priority DESC, RAND();
      LEAVE myLoop;
    END IF;
    SET testsLeft = testsLeft -1;
    SET swap = LEFT(tmpNum, CHAR_LENGTH(tmpNum)-1);
    SET tmpNum = swap;
  END WHILE ;
END ?
DELIMITER ;
