How to convert the string parameter to a column name-CodePudding

I have a stored procedure where the column name is coming in the parameter as string - varchar

How to convert this to a column name to query in the select/insert/update as column name should be colnName

Thanks

How to convert the incoming string to column name in stored procedure

CodePudding user response：

As far as a I know , column names can not be defined using a variable when executing a query. They have to be hardcoded. Therefore, we should focus on hardcoding column names when writing the statement, rather than determine the column name on the fly when executing the query. MySQL PREPARE statement is a viable choice for this. Here is the test sample if you are intrigued.

drop table if exists test;
create table test (id int);
delimiter //
drop procedure if exists str_to_colname //
create procedure str_to_colname(colnName varchar(15))
begin
set @insert_stmt=concat('insert test (',colnName,') values(1),(2),(3);'); -- we make our first statement into a user variable, which will be used as a source for PREPARE
prepare inst from @insert_stmt; -- define the prepared statement using our user variable
execute inst; -- this is like executing a query using the literal values of the user variable
deallocate prepare inst; -- remove the prepare after its execution

set @select_stmt=concat('select ',colnName,' from test;'); -- here is the user variable which stores our second statement
prepare sel from @select_stmt;
execute sel;
deallocate prepare sel;

end//

delimiter ;
call str_to_colname('id');  -- here we use the column name `id` as the parameter

--result set:
# id
1
2
3

CodePudding user response：

But you can have multiple if conditions to solve the problem.

It is possible as well

Solution 1 - SQL query on FLY - but it can only be executed Solution 2 - Dynamic SQL - where we cannot add column name as it would be a string.

We need to use CASE and WHEN and THEN and create the query (OR) IF THEN

IF(columnName='product') THEN select product from purchase IF(columnName='test') THEN select test from purchase

In this way we can solve. so both the options are possible.