CODEWITHSUNDEEP
mysqlselectuser-defined-functions
Arun Sudhakaran
Arun Sudhakaran

Reputation: 2405

Dynamically generating query using function

I just started learning use defined functions in MYSQL and I tried few basic examples, but when I tried a function which returns varchar, which actually returns a portion of a select query, it gives me something unexpected.

There are lot of tables in database with columns as id and name, so whenever people want to fetch id for a particular name or vice versa they go into the table and manually fetch the data. So I was trying to create a function that would smoothen the process.

This is my function 

create function getTableDetails(table_name varchar(20), id int(5), name varchar(20)) 
returns varchar(50)
begin
    return " * from " + table_name + " where id = " + id + 
               " or name like '%" + name + "%'";
end

As you can see I'm trying to generalize the query for all tables and it returns everything other than the select keyword.

Now my query will be like select getTableDetails('classes', 2, 'a')

Assuming that the return of the function will fill the rest of the query and give me the table data, but the result set what I'm getting is 

getTableDetails('classes'; 2; 'a')
2

getTableDetails('classes'; 2; 'a') is the header.

Where I'm getting wrong?

Upvotes: 0

Views: 59

Answers (1)

Darshan Mehta
Darshan Mehta

Reputation: 30809

You can't dynamically generate and execute query like that, from command line. You need to use preparedstatement and that too, from stored procedure, e.g.:

SET @query = CONCAT('select', getTableDetails('classes', 2, 'a'));

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Upvotes: 2

Related Questions