Adding list of GUIDs as SqlParameters to filter to perform IN operator

Question

I am trying to pass a list of GUIDs as SqlParameters to execute a query using IN operator. But the result is empty even though some of the provided GUIDs are valid as exist in the table.

Here is an example of what I have written

string query = "SELECT * FROM tblStudents WHERE studentID IN (";

List<SqlParameter> parameters = new List<SqlParameter>();
for (int i = 0; i < lstGuids.Count; i++)
{
    string paramName = "@guid" + i;
    query += paramName + ",";
    parameters.Add(new SqlParameter(paramName, SqlDbType.UniqueIdentifier) { Value = new Guid(lstGuids[i].Trim()) });
}

query = query.TrimEnd(',') + ")";

I tried the same setup on the Database using the SSMS, it works.

On further reviewing I noticed, GUIDs when added to the parameters from the code are in small case. To verify, I ran the same query on the database using SSMS, no matter the letter case, I get the same result. So something with my code that I am unable to figure that is causing the issue.

Would appreciate any inputs. Thank you.

Answer 1

One possible way would be to pack together those GUIDs into a single string, separated by e.g. a semicolon ;, and then use the STRING_SPLIT function in your query to turn that string of GUIDs into a list of GUID - something like:

string query = "SELECT * FROM tblStudents " + 
               "WHERE studentID IN (SELECT Value FROM STRING_SPLIT(@StudentIdParam, ';'))";

SqlParameter parameters = new SqlParameter>("@StudentIdParam", SqlDbType.VarChar, 1000);
parameters.Value = "guid1;guid2;guid3"; // replace with your GUIDs here

If you would use a stored procedure, then I'd recommend use the table-valued parameter type - but for an "ad-hoc" query, that option is not available, so extracting the GUIDs from a "joined string of GUIDs" seems like one feasible option.