I am using a MySQL stored function where I have created a function and I have passed column name as a parameter to this function. Suppose
CREATE DEFINER=`root`@`localhost` FUNCTION `test_func`(column_1 varchar(100)) RETURNS varchar(100) CHARSET latin1
select column_1 from table where id = 1 ;
Here I want to get the value of column 'column_1' with id 1. But I am getting the column name itself not the value.
Please help me to write the correct syntax.
There are couple of problems with your approach. First of all you can't use your argument value to reference the underlying column. Good thing is that you can use Prepared Statements as workaround for this.
Second problem is that MySQL functions don't allow use of Prepared Statements. To workaround that limitation you need to use Stored Procedures instead. As an example:
CREATE PROCEDURE test_func (IN col1 varchar(100), OUT res int)
BEGIN
SET @s=CONCAT('SELECT ',col1,' INTO @res FROM yourtable WHERE id=1');
PREPARE stmt1 FROM @s;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
SELECT @res INTO res;
END
After a long search I found that a stored function can be used only to return a single value or a calculated answer (Arithmetic). For your query just use a stored procedure. It is as simple as creating a query from the front end. And here is the syntax:
Use CONCAT(X1,X2,X3 . . .) to do it as you would like.
CREATE DEFINER=`root`@`localhost` PROCEDURE `Test`(table longtext,column longtext,e_id longtext)
SET @s=CONCAT('Select ',table,' from ', column, 'Where employee = ',e_id);
PREPARE stmt1 FROM @s;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END
I am not sure what you are trying to ask. But here is a solution using Prepared Statements:
SET @selectStatement = CONCAT('Select ', columnName1,' from tab1 where id = 1'); -- Build Select statement like this
PREPARE stmt FROM @selectStatement; -- parse and prepare insert statement from the above string
EXECUTE stmt; -- execute statement
DEALLOCATE PREPARE stmt; -- release the statement memory.
I don't know the complete scenario, but this may be avoided by changing some design.
