user1585345 user1585345 - 29 days ago 16
MySQL Question

MySql user variable ignored

The following SQL runs as expected in the workbench, but generates an error "parameter @search must be defined" when I fire it in from a C# application. Can anyone explain this?

set @search = 'b%';
select * from country where Name like @search

Answer

MySQL use the @variable_name expression as a user defined variable. C# (or the provider - not really sure which) on the other hand uses the @parameter_name to annotate parameter placeholder in the query.

If you want to use sql user defined variables, then add the following parameter to the connection string of your .Net connector connection:

Allow User Variables=True

or

AllowUserVariables=True

This option was added to connector v5.2.2