Dynamic Update statement fails in SP, works in Workbench
Posted by:
A Lincoln
Date: February 05, 2014 08:33PM
Hi,
I have a simple Stored Procedure that builds a Dynamic UPDATE SQL statement based on input parameters. When I call the stored procedure, it builds the SQL correctly but I get an Error. Here's the strange part...I output the SQL for debugging as a select...If I copy out the SQL in the select and run it in a Workbench window it works...
Procedure:
CREATE DEFINER=`cdb_admin`@`%` PROCEDURE update_item_load_control (
IN theID int
,IN theProvider varchar(64)
,IN theCategory varchar(255)
,IN theTablename varchar(255)
,IN theTotalRows int(10)
,IN theSuccessRows int(10)
,IN theFailureRows int(10)
)
BEGIN
#DECLARE EXIT handler for sqlexception set theStatusText:= "SQLException: update_item_load_control().";
DECLARE theSql VARCHAR(1000) DEFAULT '';
DECLARE theComma VARCHAR(2) DEFAULT '';
SET theSql = CONCAT( 'update load_control', ' set' );
if theProvider is not null then
SET theSql = CONCAT( theSql, ' provider=\'', theProvider, '\'' );
set theComma = ',';
end if;
if ( theCategory is not null ) then
set theSql = CONCAT( theSql, theComma, ' category=\'', theCategory, '\'' );
set theComma = ',';
end if;
if ( theID is not null ) then
set theSql = CONCAT( theSql, ' where id_row=', theID, ';' );
/* CONSOLE : Output the SQL to the screen */
select concat(' SQL [', theSql, ']');
PREPARE stmt FROM @theSql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
end if;
END
$$
# END : Stored Procedure code
The error that gets thrown back:
Error Code: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where id_row = 6' at line 1
Sample call:
CALL update_item_load_control (
5
, 'CDB'
, 'Munchkin'
, 'CDB_load_ddm_frames_lpar_config_t'
, 100
, 99
, 1
);
Select output:
# concat(' SQL [', theSql, ']')
' SQL [update load_control set provider=\'CDB\', category=\'Munchkin\' where id_row=5;]'
Thx in advance,
Adym