Re: Insert dynamic field into variable

[Peter Brawley]

Sproc development needs a testbed including a dummy table to which accidents don't matter and optional debug code. Here's such for your sproc. I added a pdebug bool param---if it's set, the sproc displays the query string, if not it executes it. Error fixes are noted in the code.

create table if not exists vessels( pkey int, s86 int );
drop procedure if exists `usp_vesselAddToSettlementCodeValue`;
delimiter go
CREATE PROCEDURE `usp_vesselAddToSettlementCodeValue`( 
  p_pKey int(12), 
  p_settCode varchar(3), 
  p_settValue decimal(12,2), 
  pdebug bool
) 
BEGIN 
  SET @c = p_settCode; -- "s06" 
  SET @v = p_settValue; -- 100 
  SET @k = p_pKey; -- 416 
  SET @startValue = 0; 
  -- 1 NEED LEADING SPACE IN " into "
  -- 2 INTO VAR NAME NOT VALUE
  SET @s1 = CONCAT("SELECT ", @c, " into @startValue FROM vessels WHERE pKey = ", @k); 
  IF pdebug THEN
    SELECT @s1;
  ELSE
    PREPARE stmt FROM @s1; 
    EXECUTE stmt; 
    SELECT @startValue; 
  END IF;
END; 
go
delimiter ;
call usp_vesselAddToSettlementCodeValue(416, 's86', 100, 1);

Once sproc creation throws no errors, call it with pdebug=0.



Edited 1 time(s). Last edit at 08/19/2019 11:03AM by Peter Brawley.