optimize dynamic sql

[Bhupesh Tripathi]

hello everyone

DELIMITER $$

DROP PROCEDURE IF EXISTS `voip`.`countryrates` $$
CREATE DEFINER=`imsbill`@`%` PROCEDURE `countryrates`(ratingid varchar(50),callto varchar(100))
BEGIN
declare abc varchar(250);
set @abc=CONCAT('Select country,',ratingid,' into @country,@rates from voip.ratelist where instr(',callto,',code)=1 limit 1');
PREPARE STMT FROM @abc;
EXECUTE STMT;
END $$

DELIMITER ;



this is my proc now what i am tryin to is i get the column to be selected(ratingid) and the number call as parameters.

Now have to compare this number with the list of the codes in the table and select the longest match

for eg if 919869356549 is the no and
this is the table stucture

country codes customer1(rates) customer2(rates)
INDIA-VSNL 919869 .00018 .0019
INDIA-BPL 919833 .0018 .00018


than comparing the no with the codes and customer1 being the column name passed

should return the first row with customer1 rates which i have implemented using INSTR and ordering the resultset and selecting the top most row

NOW my table is indexed on code but i gs since i am using a function its not used how can i optimised this

please help
thanks in advance



Edited 1 time(s). Last edit at 07/28/2006 03:52AM by Bhupesh Tripathi.