Question 87710 in ASP.NET Question posted in
The official documentation can be found here.

How to pass null in SQL IN Clause through ado.net query – Asp.net

I am passing a list of employee IDs to my ado.net query. Sometimes my list is empty so I am getting error on this condition.

The error saying: Incorrect syntax near ')'.

Here is my code:

var emps = String.Join(",", empList.Keys);
            StringBuilder sql = new StringBuilder(
                $@"select [Id]
                FROM [Test].[dbo].[Employees]
                Where [Id] NOT IN {emps}"
                );
Tags:

2

Answers


  1. 0

    Check your emp list beforehand and only add the WHERE clause to the StringBuilder if the list in not empty.
    ALSO there is an extra ‘)’ in your sql string,

    Where [Id] NOT IN {emps})

    You need to remove it.

    Login or Signup to reply.
  2. 0

    Plenty of problems in that SQL you posted, but you appear to be saying you will run this SQL over and over again, each time adding more IDs to exclude. Perhaps you do something that constantly discovers new IDs that should be ignored

    Consider:

    using var con = new SqlConnection(...);

for(..some..ongoing..iterative..process){

    empList = ..get new IDs here..

    var sql = "select [Id] from [Test].[dbo].[Employees] ";
    if(empList.Count > 0){
      sql += $"where [Id] NOT IN ({string.Join(",", empList.Select((kvp,i) => $"@p{i}"))})";
    }

    using var cmd = new SqlCommand(sql, con);

    //it's a no-op if 0 values
    cmd.Parameters.AddRange(empList.Select((kvp,i) => new SqlParameter($"@p{i}", SqlDbType.Int) { Value = kvp.Value }));

    ..execute here..
}

    Where you open and close your con rather depends on how long it takes to work out your new ids. If it’s a few milliseconds each time, it shouldn’t be a problem to open it before the loop. If getting new IDs takes long, open the con for just the time you need it

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search