stored procedures - Dynamic Table Name in If/Else clause MySQL prepared statement -

i trying pass in table name variable along other variables , create if else condition.

• first checking see if @gettable1query true rows deleted='1'
• if true nothing, else update row @gettable2 deleted='1'

this have far getting errors since syntax incorrect. appreciated. thank you.

create definer=`root`@`localhost` procedure `new_procedure`(   in tablename varchar(50),    in cartname varchar(11) ) begin set @gettable1 =   concat("select * ", tablename,          " carttype='", cartname, "' , deleted='1'"); prepare stmt1 @gettable1; execute stmt1;  if exists @gettable1 (do nothing) else begin set @gettable2 =   concat("update deleted ", tablename,          " set deleted='1'");  prepare stmt2 @gettable2; execute stmt2; end 

end

first of all, if want know if first select query returned results, use select count(*) , store in user-defined variable. if don't use select...into, it'll generate result set , stored procedure return that.

set @cartname = cartname; set @gettable1 =   concat("select count(*) @deletedcount `",          replace(tablename, '`', '``'),          "` carttype=? , deleted='1'"); prepare stmt1 @gettable1; execute stmt1 using @cartname; 

also try avoid sql injection vulnerabilities. use query parameters values, , @ least use backticks delimit table-name. escape literal backtick characters in tablename.

once have result in user-defined variable, check variable see if it's zero.

review syntax update. there's no keyword in update statement. don't name column until set clause. when in doubt, check syntax in manual: https://dev.mysql.com/doc/refman/5.7/en/update.html

if @deletedcount = 0 begin   set @gettable2 =     concat("update `",          replace(tablename, '`', '``'),          "` set deleted='1'"         -- where...?    );     prepare stmt2 @gettable2;   execute stmt2; end 

is there supposed clause carttype in update? seems there should be, otherwise you'll update rows all cart types. nothing in syntax use makes update apply matching rows first select. each sql statement context-free (mysql not support updatable cursors).