Send parameters for IN operator in sql using parameterized queries in c#

Question

I am using asp.net , C# to make my app.

In there for database oprations I am suing parametrised queries.

Here is a code

mySqlCommand = new MySqlCommand();
mySqlCommand.Parameters.AddWithValue("@ids", ids);

switch (privilegeType.ToString())
{
    case "CorporateLead":
        myQuery = @"SELECT 
  leavea.applied_leave_id_pk,
  leavea.emp_id_fk,
  leavea.emp_name AS NAME,
  leavea.start_date,
  leavea.end_date,
  leavea.is_half_day,
 ..............
FROM
  lea_applied_leave AS leavea 
  INNER JOIN emp_employee_master AS emp 
    ON emp.employee_master_id_pk = leavea.emp_id_fk 
WHERE emp.`corporate_id_fk` IN (@ids) ;

In there ids will include (10,11,12)

Ids is a string. Parameter counter will be vary according to the login user. I pass that string as a parameterized query.

But when app execute this it only getting the result which belongs to id of 10.

When I execute this code directly on MySQL it shows correct result.

So what is the wrong here? Is there any way to send parameters for IN operator?

Answer 1

Parameters are not a substitution for string operations. It takes the value 'as-is'.

That said, your SQL should read like this:

WHERE emp.`corporate_id_fk` IN (@id1, @id2, @id3) ;

Add a separate parameter for each value in your SQL statement.