Can I use a parameter for a colum name ?
testing against the sakila customer table
In MySql Workbench the SP is:
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `test_procedure`(IN ColumnNameParam varchar(10) )
BEGIN
set @ColumnName = ColumnNameParam;
set @s = CONCAT("UPDATE customer set "+@ColumnName+" = Test of ColumnNameParam
WHERE customer_id = 1");
PREPARE stmt FROM @s;
EXECUTE stmt;
END
=======================================
using Visual C# as front end:
MySqlConnection connection = new MySqlConnection(MyConnectionString)
MySqlCommand cmd = new MySqlCommand("test_procedure", connection);
connection.Open();
string sColumnNameParam = "first_name";
cmd.Parameters.AddWithValue("@ColumnNameParam", sColumnNameParam);
cmd.Parameters["@ColumnNameParam"].Direction = ParameterDirection.Input;
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
connection.Close();
==========================================================================
Get exception: MySql.Data.MySqlClient.MySqlException
Message="You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0' at line 1"
Source="MySql.Data"
ErrorCode=-2147467259
Number=1064
============================================================================
Does anyone see my syntax error ? single quotes vs double quotes vs backtick vs ?
Subject
Views
Written By
Posted
Can I use a parameter for a colum name ?
2117
December 07, 2012 12:19PM
1063
December 07, 2012 06:42PM
1112
December 08, 2012 12:42PM
1103
December 08, 2012 02:07PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.