MySQL Forums
Forum List  »  Stored Procedures

Can I use a parameter for a colum name ?
Posted by: Burt Stewart
Date: December 07, 2012 12:19PM

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 ?

Options: ReplyQuote


Subject
Views
Written By
Posted
Can I use a parameter for a colum name ?
2117
December 07, 2012 12:19PM


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.