I want to rename the columns of a query by using user-defined variables.
For example:
select id as @rt from table_name
Add a comment
|
1 Answer
This is not possible. You would have to create the sql string dynamically.
C#:
string columnName = "xy";
string sql = "SELECT id AS " + columnName + " FROM table_name";
VB:
Dim columnName As String = "xy"
Dim sql As String = "SELECT id AS " & columnName & " FROM table_name"
You can also do that in a stored procedure with MySql. See MySql documentation for SQL Syntax for Prepared Statements
It would look something like this (not tested):
CREATE PROCEDURE myProc (columnName VARCHAR(30))
BEGIN
SET @sql = CONCAT("SELECT id AS ", columnName, " FROM table_name");
PREPARE s1 FROM @sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
END$$
answered Jan 8, 2012 at 16:41
Olivier Jacot-Descombes
114k14 gold badges149 silver badges202 bronze badges
2 Comments
Andriy M
In an answer of this kind, one should hardly be afraid of saying too much about SQL injection. At least, it can do no harm to mention the possible risk thereof.
Olivier Jacot-Descombes
SQL injection sould not be a problem here, if the column names are defined by the programmer rather than entered by a user. In the latter case names would have to be tested for validity.