CREATE PORCEDURE spInsST()
BEGIN
DECLARE v_ContractID INT;
DECLARE v_SecurityTypeID INT;
DECLARE v_ExchangeID INT;
DECLARE v_VarietyID INT;
DECLARE v_CurrencyID INT;
DECLARE v_underlying text;
DECLARE v_sec text;
DECLARE v_exch INT;
DECLARE v_exchangeName text;
DECLARE v_currency INT;
DECLARE v_fxrate DECIMAL(20,8);
DECLARE v_commfee DECIMAL(20,8);
DECLARE v_commFlag INT;
DECLARE fig INT;
DECLARE cur1 CURSOR FOR SELECT ContractID,SecurityTypeID,ExchangeID,VarietyID,CurrencyID,IFNULL(CommissionFee,0) AS comm1 FROM contractinfo ORDER BY ContractID;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET fig=1;
OPEN cur1;
REPEAT
FETCH cur1 INTO v_ContractID,v_SecurityTypeID,v_ExchangeID,v_VarietyID,v_CurrencyID,v_commfee;
SELECT VarietyNum INTO v_underlying FROM contractvariety WHERE VarietyID=v_VarietyID;
UPDATE strategycontracttemp SET underlying=v_underlying WHERE id=v_ContractID;
SELECT DValue INTO v_sec FROM  dictionary WHERE DID=v_SecurityTypeID;
UPDATE strategycontracttemp SET sec=v_sec WHERE id=v_ContractID;
SELECT DValue,DName INTO v_exch,v_exchangeName FROM dictionary WHERE DID=v_ExchangeID;
UPDATE strategycontracttemp SET exch=v_exch WHERE id=v_ContractID;
UPDATE strategycontracttemp SET exchangeName=v_exchangeName WHERE id=v_ContractID;
SELECT DValue INTO v_currency FROM dictionary WHERE DID=v_CurrencyID;
UPDATE strategycontracttemp SET currency=v_currency WHERE id=v_ContractID;
SET v_fxrate=(CASE v_CurrencyID WHEN 150001 THEN '6.239'
                                WHEN 150002 THEN '7.761'
                                WHEN 150003 THEN '1'
                                WHEN 150004 THEN '77.61'
                                WHEN 150005 THEN '0.6376'
                                WHEN 150006 THEN '0.7655'
                                WHEN 150007 THEN '0.9354'
                                WHEN 150008 THEN '0.989'
                                WHEN 150009 THEN '0.9129'
                                WHEN 150010 THEN '1.2603' END); 
IF v_commfee=0 THEN SET v_commFlag=1; 
ELSE SET v_commFlag=2;
END IF;
UNTIL fig=1
END REPEAT;
CLOSE cur1;
END;