I am not sure why you do not just pull the information from the database and from your application generate the HTML. If your client application is on a separate server to your database server you are transferring a lot more data by having the database generate the HTML than generating it yourself in your application. This will be more scalable too.
It's a strange request, so there is no wonder you ask why. There is no application in this case that deals with the representation layer because I am going to use this table-generating UDF for documents. It would be a very convenient tool when I copy result sets from the terminal into HTML or LaTeX documents.
Surely, I can write tables in docs using "export as HTML" command. I won't die for it but it'd be a lot nicer if I don't have to save "resultset.html" file every time I have a table to copy.
Maybe it is more a life-hack than a DB question. I should feel very lucky if someone ever holds my hand while I write an all-query solution. Still, I am wondering how, if at all, infinite arguments are possible in SQL functions as they are in programming languages.
It took me a long time to create something very simplistic. At one time I tried to concatenate columns with a CASE expression and the got a collation error. So here is my code, albeit very unsatisfactory:
DROP PROCEDURE IF EXISTS html_table;
CREATE PROCEDURE html_table(IN listOfColumns VARCHAR(100), tableName VARCHAR(100))
SET @query := CONCAT("SELECT CONCAT('<tr>', CONCAT('<td>', CONCAT_WS('</td><td>',", listOfColumns, "), '</td>'), '</tr>') htmlTableTags FROM ", tableName);
PREPARE stmt FROM @query;
DEALLOCATE PREPARE stmt;
It takes column names and the table name and executes an unconditional SELECT statement (no JOIN or WHERE). Test results are as follows: