I want to make a procedure that has column and table as variables-CodePudding

I'm painfully new to SQL/mySQL as a whole so I'm flying blind right now so apologies.

I made a procedure in mySQL that selects a varchar data from a specific column and table, turn it into INT (contents of said column are numerical to begin with) and output its values after going through a mathematical operation as a (very simple) attempt in data masking. As follows:

CREATE PROCEDURE qdwh.mask_varchar_num2(tablename varchar(100), colname varchar (100))
BEGIN
set @a=concat('select','(','(','(','(','select',colname ,'from',tablename,')',' ','0',')',' ','297',')','*','5',')','as','colname');
prepare query from @a;
execute query;
deallocate prepare query;
END

but when i tried to call the procedure with the following line:

select [column] , mask_varchar_num2 ([column]) from [table];

an error "FUNCTION qdwh.mask_varchar_num2 does not exist" shows up. I wanted the script to output a select function of the column in question after the conversion to INT and the mathematical operation done to it, so i can then use this procedure in a larger script ("create table select as" kinda stuff) to convert the whole table into masked data as needed.

Is there something i am missing and what am i doing wrong? Dbeaver acknowledges the procedure script as legit so i dont know whats wrong. Thanks in advance for the advice.

CodePudding user response：

Procedures are run by using call and cannot be called within a select query. To define a function, you need to use create function.

CodePudding user response：

not an answer but here's what your select looks like..

set @colname='a';
set @tablename='t';

set @a=concat('select','(','(','(','(','select',@colname ,'from',@tablename,')',' ','0',')',' ','297',')','*','5',')','as','colname');

select @a


'select((((selectafromt) 0) 297)*5)ascolname'

missing a lot of spaces between tokens