How to find nearest value from mysql database table of give value, what i want to do is:
CASE1: if i am giving input 2500 to pincode then i am retrieving record corresponding to that pincode value from database.
CASE2: if record not exits for entered pincode(2500) , i have to get nearest value of 2500 and its corresponding records too.
Stored procedure i am currently calling for CASE1 sp_findlocation('XXXXXXXX','2500',@addresss)
DELIMITER $$
USE `test`$$
DROP PROCEDURE IF EXISTS `sp_findlocation`$$
CREATE PROCEDURE `sp_findlocation`(IN mobile VARCHAR(20),IN pincode VARCHAR(20),OUT address VARCHAR(100))
BEGIN
IF(pincode IS NOT NULL AND pincode!= '') THEN
SET @c1 = '';
SET @c2 = '';
SET @c3 = '';
SET @qry='select locality,store_name,contact_number INTO @c1,@c2,@c3 from test_upload where pin=? limit 1';
PREPARE stmt FROM @qry;
SET @c = pincode;
EXECUTE stmt USING @c;
DEALLOCATE PREPARE stmt;
IF(@c1!='' || @c2!=''||@c3!='') THEN
SET address= CONCAT(@c1, '|',@c2,'|',@c3);
ELSE
SET address= "No Result";
END IF;
ELSE
SET address="Please enter valid pin code";
END IF;
END$$
DELIMITER ;
what i will do for geting records including CASE2?
