How to Select an out parameter from a MySql Procedure in .net

Question

Assume we have a stored procedure like so

CREATE PROCEDURE CopyValue(IN src INT, OUT dest INT)
BEGIN
    SET dest = src;
END

I want to call this from a .net app (assume connection etc created successfully)

var sql = "call CopyValue(100, @destValue); select @destValue as Results;";

The string in the above statement works perfectly well when called in MySql Workbench.

However this - obviously - fails with "MySqlException: Parameter '@destValue' must be defined" when executed on a MySqlCommand object in .net

How do I arrange this statement so I can capture an output parameter from an existing procedure?

NB: I'm running against MySql 5.6, which I can't upgrade at this time.

NB Calling the procedure directly with CommandType.StoredProcedure goes against company guidelines.

Answer 1

A colleague (who wishes to remain anonymous) has answered this perfectly. Essentially put backticks ` after the @ and at the end of the variable name e.g.

@`MyParam`

A fully working example.

static void Main(string[] args)
{
    using var con = new MySql.Data.MySqlClient.MySqlConnection("Data Source=localhost; User Id=...;Password=...;Initial Catalog=...");
    con.Open();
    using var cmd = con.CreateCommand();
    cmd.CommandText = "call CopyValue2(100, @`v2`); select @`v2` as Results;";
    using var reader = cmd.ExecuteReader();
    if (reader.Read())
        Console.WriteLine($"Copied Value {reader.GetInt64(0)}");
}

Thanks OG :)

Answer 2

By default, user-defined variables aren't allowed in SQL statements by MySQL Connector/NET. You can relax this restriction by adding AllowUserVariables=true; to your connection string. No modifications to your SQL or how you're executing the MySqlCommand should be necessary.

For information about why this is the default, you can read the research on this MySqlConnector issue (which also has the same default behaviour, but a much better error message that will tell you how to solve the problem): https://github.com/mysql-net/MySqlConnector/issues/194