Execute stored procedure updating same table thrice with timeout 600

Certified Senior Developer

Hello

I have created a stored procedure

DELIMITER $$
CREATE OR REPLACE DEFINER=`dbadmin`@`%` PROCEDURE `FAR_STAGE_BP_IMPACT`()
BEGIN
		UPDATE FAR_BREAKS_STAGE cod
		INNER JOIN FAR_IBOR_BP_IMPACT_DATA tna ON (cod.FUND_SYMBOL = tna.ACCT_CD_FMRID)
		SET 
		cod.IBOR_TOTAL_NET_ASSETS_USD = tna.MKT_VAL_USD,
			cod.BREAK_BP_IMPACT =
			CASE
				WHEN 
				IFNULL(cod.IBOR_TOTAL_NET_ASSETS_USD,0) = 0 THEN
					0
					WHEN cod.DEBIT_CREDIT = 'Debit' THEN
					CAST(IFNULL(((cod.USD_EQ_Amount / cod.IBOR_TOTAL_NET_ASSETS_USD) * -10000), 0) AS DOUBLE(10, 4))
				ELSE
					CAST(IFNULL(((cod.USD_EQ_Amount / cod.IBOR_TOTAL_NET_ASSETS_USD) * 10000), 0) AS DOUBLE(10, 4))
				
			END
		WHERE
			tna.ACCT_CD_FMRID IS NOT NULL and cod.REC_TYPE = 'IBOR';
			
					
			UPDATE FAR_BREAKS_STAGE cod
		INNER JOIN FAR_ABOR_BP_IMPACT_DATA tna ON (cod.FUND_NUMBER = tna.ACCOUNT_NUMBER_IO)
		SET 
		cod.ABOR_TOTAL_NET_ASSETS_USD = tna.TOTAL_NET_ASSETS_USD,
		cod.BREAK_BP_IMPACT =
			CASE
				WHEN 
				IFNULL(cod.ABOR_TOTAL_NET_ASSETS_USD,0) = 0 THEN
					0
				WHEN cod.DEBIT_CREDIT = 'Debit' THEN
					CAST(IFNULL(((cod.USD_EQ_Amount / cod.ABOR_TOTAL_NET_ASSETS_USD) * -10000), 0) AS DOUBLE(10, 4))
				ELSE
				 CAST(IFNULL(((cod.USD_EQ_Amount / cod.ABOR_TOTAL_NET_ASSETS_USD) * 10000), 0) AS DOUBLE(10, 4))
			END
			
		WHERE
			tna.ACCOUNT_NUMBER_IO IS NOT NULL and cod.REC_TYPE = 'ABOR';
			
          UPDATE FAR_BREAKS_STAGE bs SET 
			bs.FUND_BP_IMPACT =
			CASE WHEN 
			 bs.REC_TYPE= 'IBOR' THEN
			 fn_get_sum(bs.FUND_SYMBOL, 'IBOR')
			ELSE
		  fn_get_sum(bs.FUND_SYMBOL, 'ABOR') 
          END;
			
                
END$$
DELIMITER ;

This stored procedure is working in unexpected manner. Sometimes only updating ABOR data and sometimes only IBOR and not updating column FUND_BP_IMPACT column at all. timeout is set as 600ms. Is there any way we can increase the timeout and can anyone suggest that do I need to split this one procedure into three to work correctly?

  Discussion posts and replies are publicly visible

Parents Reply Children
  • 0
    Certified Senior Developer
    in reply to Stefan Helzle

    when i am running these three update statements in stored procedure, it is updating only IBOR_TOTAL_NET_ASSETS_USD and ABOR_TOTAL_NET_ASSETS_USD but not updating Break_BP_Impact and FUND_BP_IMPACT column, they are all 0.0000 or null. But when I run these three update statements separately one by one in sequence in SQL manually, it is working correctly and updating all columns. Why it is not working in the stored procedure????