MySQL Forums
Forum List  »  Stored Procedures

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Dynamic Update statement fails in SP, works in Workbench
3746
February 05, 2014 08:33PM


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.