Reputation: 1031
I am stuck in Mysql today with the dynamic column name need in mysql select statement. Let me explain:
sql> select name1 from namescollection.
sql> select name2 from namescollection.
sql> select name3 from namescollection.
So namescollection table has three columns having name1, name2, name3 I would like to query this table in my stored procedure being 1,2,3 as dynamic and would be passed as a variable, but on the simple sql too when i query:
SELECT concat('name','1') FROM `namescollection`
name1 ----- name1 rather fetching name1 field's value.
Can any suggest on this, the right function i need to use rather concat though I know its right to output name1 when I am calling concat but I want to use it as column name.
Upvotes: 6
Views: 16098
Reputation: 166
Dynamic column name in store procedure Update statement :
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `selname`()
BEGIN
declare cl int ;
declare clvalue int ;
set cl=1;
SET clvalue=1;
while cl < 4 DO
SET clvalue=clvalue*cl;
SET @sql = CONCAT('update test set col',cl, '=',clvalue,' where id=1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
set cl=cl+1;
end while;
END //
DELIMITER ;
Upvotes: 5
Reputation: 23135
What you can do is use a prepared statement within your stored procedure which will allow you to execute a string query:
As a simple example:
DELIMITER //
CREATE PROCEDURE selname (IN col VARCHAR(20))
BEGIN
SET @sql = CONCAT('SELECT ', col, ' FROM tbl');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END//
DELIMITER ;
Test it out with this SQLFiddle Demo
Upvotes: 15