Pass ANY string as a in parameter to a stored procedure in mysql (dynamic)

Question

I am experiencing some trouble when I pass date-like strings to a input parameter of a stored procedure.

The table I try to modify has following columns:

create table localdevid.product_a(
INDX int PRIMARY KEY NOT NULL AUTO_INCREMENT,
ProdID int unsigned,
Assigned tinyint,
TesterID varchar(8),
tAss datetime);

Now I try to create a stored procedure:

use localdevid;
drop procedure if exists AssignNewDevID;

DELIMITER $$

use localdevid$$
CREATE PROCEDURE AssignNewDevID(in TableName varchar(255), in TesterName varchar(8), out DevID bigint(20))

BEGIN
    #declare rightnow datetime;
    
    set @t1=CONCAT("select SensorID into @localID from localdevid.",TableName," where ISNULL(Assigned) and INDX>1 order by INDX asc limit 1 for update");
    prepare statement1 from @t1;
    execute statement1;
    deallocate prepare statement1;
    set DevID=@localID;
    set @t2=CONCAT("update localdevid.",TableName," set Assigned=4 where SensorID=",DevID);
    prepare statement2 from @t2;
    execute statement2;
    deallocate prepare statement2;

    set @t3=CONCAT("update localdevid.",TableName," set TesterID=",TesterName," where SensorID=",DevID);
    prepare statement3 from @t3;
    execute statement3;
    deallocate prepare statement3;
    
    commit;
    
    
END $$
DELIMITER ;

There were several issues, therefore I splitted it into the three statements to see where my problems might come from. I surely will later get it back into one statement back later on.

If I call the function the failure message changes:

call AssignNewDevID("product_a",'tester3',@id);

The script runs to statement2, this is executed successfully. Statement3 drops Error Code 1054: "Unknown column 'tester3' in Field list. I cannot understand why the parameter is interpreted as a field name. It gets even stranger, if I pass a string as TesterName, which can be interpreted as a date or time. In example, the TesterName are usually MAC-IDs, so the string is i.e. "00:08:01" (I transfer only the last 3 bytes of the MAC). If I call it like this:

call AssignNewDevID("htpa32x32d",'00:08:01',@id);

I get error code: 1064: You have an error in your SQL syntax; check the manual...

What I am doing wrong here? Why can I concat TableName and DevID but not TesterName? I don't see any difference here to the the other parameters.

Furthermore, I was not able to pass the current datetime to tAss. I did try the following:

    declare rightnow datetime;
    declare mydate varchar(20);
    select DATE_FORMAT(now(),"%d.%m.%y") as mydate;
    ...
    set @t4=CONCAT("update localdevid.",TableName," set tAss=",mydate," where SensorID=",DevID);

How can I pass basically NOW() to tAss?

score 0 · Accepted Answer · answered Jul 13 '22 at 06:13

OK, got it. Since I pass a string in TesterName I do need of course mark it between 'xxx' in this case. So it works by

set @t3=CONCAT("update localdevid.",TableName," set TesterID='",TesterName,"' where SensorID=",DevID);

Same applies for the timestamp:

set @mydate=DATE_FORMAT(now(),"%d.%m.%y %h:%i:%S");
set @t4=CONCAT("update localdevid.",TableName," set tAss='",@mydate,"' where SensorID=",DevID);

Pass ANY string as a in parameter to a stored procedure in mysql (dynamic)

1 Answers1