MySQL Forums
Forum List  »  Stored Procedures

Re: Insert dynamic field into variable
Posted by: Peter Brawley
Date: August 19, 2019 11:03AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
840
August 19, 2019 10:26AM
Re: Insert dynamic field into variable
282
August 19, 2019 11:03AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.