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.