how can i set the parameters for the sql query optional?

Question

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.

Dump your query, for example you may have this: "...FROM RAUM r WHERE RAUMKLASSE_ID IN ()..." which is invalid. You should append each constrain only if the parameter has a value. — Adriano Repetti, Jun 19 '12 at 11:02
i don't know what you mean. could you explain it to me with more details? — Eray Geveci, Jun 19 '12 at 11:15

Adriano Repetti · Accepted Answer · 2022-01-18T14:07:32.850

2

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

edited Jan 18 '22 at 14:07

answered Jun 19 '12 at 11:25

Adriano Repetti

65,416
20
137
208

thanks i tried your code but i still get the same error. i don't know why – Eray Geveci Jun 19 '12 at 13:45
@ErayGeveci log query.ToString() and post here. – Adriano Repetti Jun 19 '12 at 14:08
i don't know how to log a webservice which is on a server. ill search for a solution and write post you the results – Eray Geveci Jun 19 '12 at 14:14
@ErayGeveci You can use System.Diagnostics.EventLog. For this test you can even...throw an exception with the query itself as message! – Adriano Repetti Jun 19 '12 at 14:17
but i can't use it when i public the project in a folder and copy it manually to the server. i can't test it within the visual studio because the database exist only on the server – Eray Geveci Jun 19 '12 at 14:29
@ErayGeveci you do not need to test the function in the web service. Copy & paste the function in a test program (leaving only query creation code), call with the same parameters and dump the output. – Adriano Repetti Jun 19 '12 at 14:30
the output is: SELECT r.BEZEICHNUNG AS BEZEICHNUNG, r.ID AS ID FROM RAUM r WHERE RAUMKLASSE_ID IN (2) AND STADT_ID IN () AND GEBAEUDE_ID IN () AND REGION_ID IN () – Eray Geveci Jun 19 '12 at 14:51
i think the problem is that your code still generates the "AND" parameters, even if they are empty – Eray Geveci Jun 19 '12 at 14:52
@ErayGeveci thank you, you're right! Change String.IsNullOrWhiteSpace(name) with String.IsNullOrWhiteSpace(value). I updated the code. – Adriano Repetti Jun 19 '12 at 14:53
could you explain me why i can use a comma separated string as a parameter without splitting? why is this working? – Eray Geveci Jun 20 '12 at 12:43
@ErayGeveci because it's the syntax of the IN clause (http://msdn.microsoft.com/en-us/library/ms177682.aspx). It accepts a **list** of allowed values (for a single value you should use a simple "=" operator). Please note that it accepts a query too so if that parameters have been inserted by the user they must be VALIDATED to protect against SQL injection. – Adriano Repetti Jun 20 '12 at 12:53

score 0 · Answer 2 · answered Jun 19 '12 at 11:20

It always be a better approach to write the stored procedures and pass the parameters. But in your approach you should split your query because of not sure the values. So, your code be something like that..

Test it yourself, i didnt check it

string raumklasseid = RAUMKLASSE_ID;
        string gebaudeid = GEBAEUDE_ID;
        string stadtid = STADT_ID;
        string regionid = REGION_ID;
        string whereClause = string.Empty;

if (!string.IsNullorEmpty(raumklasseid))
{
   whereClause = "RAUMKLASSE_ID IN (" + raumklasseid + ")";
}
if (!string.IsNullorEmpty(stadtid ))
{
   if(string.IsNullorEmpty(whereClause)
      whereClause = "STADT_ID IN (" + stadtid + ")";
   else 
      whereClause += "AND RSTADT_ID IN (" + stadtid + ")";
}
if (!string.IsNullorEmpty(stadtid ))
{
   if(string.IsNullorEmpty(whereClause)
      whereClause = "STADT_ID IN (" + stadtid + ")";
   else 
      whereClause += "AND RSTADT_ID IN (" + stadtid + ")";
}
if (!string.IsNullorEmpty(regionid))
{
   if(string.IsNullorEmpty(whereClause)
      whereClause = "REGION_ID IN (" + regionid + ")";
   else 
      whereClause += "AND REGION_ID IN (" + regionid + ")";
}

if(!string.IsNullorEmpty(whereClause)
whereClause = "WHERE " + whereClause ;

// now your cmd should be like that

using (SqlCommand cmd = new SqlCommand(@"SELECT r.BEZEICHNUNG AS BEZEICHNUNG, r.ID AS ID FROM RAUM r " + whereClause , con))

how can i set the parameters for the sql query optional?

2 Answers2

Linked