Question 21908 in Mysql Question posted in
The official MySQL documentation can be found here.

I want to make a procedure that has column and table as variables – Mysql

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.

Tags:

2

Answers


  1. 0

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

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search