How to handle dynamic sql parameters
Solution 1
Depending on the specific implementation, we have two general approaches to this problem:
1) Dynamically build the filter statement for the SQL query in code skipping any parameters that are empty. This is the best approach if you allow the user to select multiple values for a single column (i.e. select 0 or more of the 50 states to filter the data).
For example:
Assuming txtCondition1 and txtCondition2 are textboxes:
// Assuming conn is an open SqlConnection
System.Text.StringBuilder sbSQL = new StringBuilder(500);
List<SqlParameter> cParameters = new List<SqlParameter>();
// Add a default condition of 1=1 so that all subsequent conditions can be added
// with AND instead of having to check to see whether or not any other conditions
// were added before adding AND.
sbSQL.Append("SELECT * FROM MyTestTable WHERE 1 = 1 ");
if (!String.IsNullOrEmpty(txtCondition1.Text)) {
sbSQL.Append(" AND Column1 = @Column1");
cParameters.Add(new SqlParameter("@Column1", txtCondition1.Text));
}
if (!String.IsNullOrEmpty(txtCondition1.Text))
{
sbSQL.Append(" AND Column2 = @Column2");
cParameters.Add(new SqlParameter("@Column2", txtCondition2.Text));
}
SqlCommand oCommand = new SqlCommand(sbSQL.ToString, conn);
if (cParameters.Count != 0)
{
oCommand.Parameters.AddRange(cParameters.ToArray());
}
// Do something with oCommand
2) If the values are more constrained, we usually pass them to a stored procedure, which is responsible for determining whether or not the value is to be evaluated by testing the parameter for "emptinesss", either null, empty string, 0 for numerics, etc.
Solution 2
One of the things that can be done is check whether the parameter was passed to your stored procedure. You can do it like this:
create procedure my_procedure (
@param1 as int = null
@param2 as int = null
) as
begin
select field1, field2, fieldn
from table
where ((@param1 is null) or (field2 = @param1))
and ((@param2 is null) or (field2 = @param2))
end
I'd rather do this on sql procedure than in the application tho
chobo
Updated on June 26, 2022Comments
-
chobo about 2 years
What is a good way to handle dynamic sql parameters?
I have a search form that takes in a whole bunch of different search parameters. If the parameters are empty and I have the parameter in the sql string will it screw or slow down the query?
-
Mike Christensen over 12 yearsCan't you build the SQL string dynamically as well?
-
MilkyWayJoe over 12 yearsI think it costs more to build the query in the application than having a the
where
clause to perform these checks. doing this on the app, you have to evaluate and check values for all possible parameters and execute logic to create the appropriate query, and depending on the database you would prefer to have a static query for performance (execution plan.. etc)
-
-
chobo over 12 yearsHow would I prevent sql injection on a dynamically created search filer statement?
-
competent_tech over 12 yearsTwo ways: 1) There is nothing preventing you from using parameters within the dynamic SQL statements, which definitely helps; 2) The only values that you would have to check would be those where the user could enter free-form text. The way that we handle this is to double-up all single quotes and replace any embedded semi-colons with spaces.
-
chobo over 12 yearsCould you provide a simple example of point 1 with sql parameters? That's what I would like to try and get working
-
Mike Christensen over 12 yearsOne thing that might come back to bite you is T-SQL doesn't short circuit
OR
clauses. If youOR
it with a function that doesn't like NULL values (such as several of the full text search operators), you'll get an error. -
MilkyWayJoe over 12 yearsThat's true. I haven't included any logic to handle this for this sample, but what I usually do is handle all parameter before I call any query. So the select above is missing the logic to check all possible issues and reassign the params to safe values.
-
Mike Christensen over 12 years
+1
for this answer, I do almost exactly this for the search feature on my own website. -
chobo over 12 yearsJust noticed that you cannot instantiate the sqlparametercollection class. I switched out the SqlParameterCollection for a List<SqlParameters>
-
competent_tech over 12 years@chobo: Sorry, was going from memory. It should be List<SqlParameter>, not SqlParameterCollection. I have updated the answer.