[FIXED] how can i set the parameters for the sql query optional?

Issue

I build a Web Service in ASP.Net which sends me a list of rooms.

The parameters are id's which are separated by a comma.

I saved them to a string and build a sql select query.

When I send all 4 parameters I everything works fine and I get a result. But when I send less then 4 I get an error.

System.Data.SqlClient.SqlException: Incorrect syntax near ')'.

How can I set my the parameters optional in the sql query to select just the values I entered?

Here is my code so far:

internal static List<RAUM> Raum(string RAUMKLASSE_ID, string STADT_ID, string GEBAEUDE_ID, string REGION_ID)
{
    List<RAUM> strasseObject = new List<RAUM>();
    string raumklasseid = RAUMKLASSE_ID;
    string gebaudeid = GEBAEUDE_ID;
    string stadtid = STADT_ID;
    string regionid = REGION_ID;

    using (SqlConnection con = new SqlConnection(@"Data Source=Localhost\SQLEXPRESS;Initial Catalog=BOOK-IT-V2;Integrated Security=true;"))
    using (SqlCommand cmd = new SqlCommand(@"SELECT r.BEZEICHNUNG AS BEZEICHNUNG, r.ID AS ID FROM RAUM r WHERE RAUMKLASSE_ID IN (" + raumklasseid + ") AND STADT_ID IN (" + stadtid + ") AND GEBAEUDE_ID IN (" + gebaudeid + ") AND REGION_ID IN (" + regionid + ")", con))
    {
        con.Open();

        using (SqlDataReader rdr = cmd.ExecuteReader())
        {
            while (rdr.Read())
            {
                if (rdr["BEZEICHNUNG"] != DBNull.Value && rdr["ID"] != DBNull.Value)
                {
                    strasseObject.Add(new RAUM()
                    {
                        RaumName = rdr["BEZEICHNUNG"].ToString(),
                        RaumID = rdr["ID"].ToString()
                    });
                }
            }
        }
    }

    return strasseObject;
}

Thanks in advance for your help.

Solution

Imagine the parameter REGION_ID is an empty string. That part of your query will be something like:

...AND REGION_ID IN ()...

Because in AND REGION_ID IN (" + regionid + ")" the regionid variable will be replaced with an empty string. This is not valid SQL syntax so you'll get that exception.

Declare a function like this:

private static void AppendConstrain(StringBuilder query, string name, string value)
{
    if (String.IsNullOrWhiteSpace(value))
        return;

    if (query.Length > 0)
        query.Append(" AND ");
    
    query.AppendFormat("{0} IN ({1})", name, value);
}

Then change your code to build the query in this way:

StringBuilder constrains = new StringBuilder();
AppendConstrain(contrains, "RAUMKLASSE_ID", RAUMKLASSE_ID);
AppendConstrain(contrains, "GEBAEUDE_ID", GEBAEUDE_ID);
AppendConstrain(contrains, "STADT_ID", STADT_ID);
AppendConstrain(contrains, "REGION_ID", REGION_ID);

StringBuilder query =
    new StringBuilder("SELECT r.BEZEICHNUNG AS BEZEICHNUNG, r.ID AS ID FROM RAUM r");

if (constrains.Length > 0)
{
    query.Append(" WHERE ");
    query.Append(constrains);
}

using (SqlCommand cmd = new SqlCommand(query.ToString(), con))
{
    // Your code...
}

WARNING: DO NOT USE this code in production or when the input comes from the user because it's vulnerable to SQL injection. For better approaches (do not stop to the accepted answer) see Parameterize an SQL IN clause

Answered By - Adriano Repetti

Answer Checked By - Willingham (PHPFixing Volunteer)