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

Mysql – How to convert the string parameter to a column name

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

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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.


  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search