Creating Dynamic SQL statements that reflect user form selections at SQLBook.com
A frequent business requirement is the implementation of a form that allows a user to filter data. For example a 'Customer search' form may allow an employee to enter a customers forename and / or surname and return a list of customers names that matched the forename and/or surname that was entered.
In order to filter the data a WHERE clause must be implemented in the database query to filter the data according to the users chosen criteria. This can be done in a number of ways that are outlined below.
Using string concatenation and the EXEC command
String concatenation means joining seperate strings together. A common way of implementing a dynamic SQL statement is by using string concatenation to build a SQL statement and then to execute this statement. As example of building a dynamic SQL statement using string concatenation is demonstrated below. Concatenation means to join seperate strings together. The example below first creates a table, add a couple of records into it and then dynamically builds a SELECT statement to query this new table.
While this method or dynamic string concatenation works it has the following disadvantages:
- Can result in a lot of SQL code to write, especially when a large number of report parameters are specified
- Can result in hard to read code
- Can lead to headache causing data type conversion errors when trying to concatenate variables of different data types
- Cannot have a execution plan saved thus slowing repeat use
Using the COALESCE function for much a more efficient Dynamic WHERE clause
If your SQL statement needs to be dynamic but only the WHERE clause needs to be dynamic then you can use the COALESCE function to create statement that has the following advantages:
- Quicker to write
- Easier to debug
- No data type conversion issues
- Can have its execution plan stored for repeated use
The COALESCE function takes a comma delimited list of arguments and returns the first argument (starting from the left) that is NOT NULL. e.g.
To use the COALESCE function in our dynamic WHERE clause we can pass the COALESCE function:
- the variable that we are checking to see whether it holds a value
- the column name of the value that we are comparing our variable against
This means that if a Non NULL value is passed in this will be used in the comparison filter but if a NULL value is passed in then the column value will be used to compare against itself which will always return True so all rows will be returned for that comparison filter. We can see how this works in the following example which is the much simpler and more efficient way of coding the string concatenation example above:
Using SELECT Case for detecting empty strings or 'Open' filters
Whilst the COALESCE function is great when you are comparing values that may contain NULL it isn't much help at detecting empty strings. Often a web application might pass in the search form criteria to a stored procedure and if the user didn't enter a value into a textbox then the stored procedure will receive an empty string rather than a NULL value. If this is the case then we can use the SELECT CASE statement to weed out these empty strings. Similar to the COALESCE method of dynamic query building, if the variable value is an empty string, the column value is compared against itself and so will always return true. Continuing with our example above we could amend it to handle empty string values rather than NULL values as follows:
We can even modify the CASE statements to also check for NULL values to cover all possibilities:
We can also check for other data type values using SELECT CASE. For example an integer variable passed from a drop down list might hold the value 0 for the list item "-- All countries--". We would use the CASE statement to get all customers when 'All Countries' was the item selected by using the following statement
In this article we have demonstrated 3 different methods for dynamically generating SQL queries, string concatenation, using the COALESCE function and the CASE statement. All have their advantages and disadvantages. String concatenation should be used when you want to dynamically specify parts of the SQL statement that are not part of the WHERE clause such as table names.
COALESCE should be used when you are building a dynamic WHERE clause only and you are expecting NULL values to be used to determine whether a field be included in the WHERE clause.
SELECT CASE is the most flexible approach when building a dynamic WHERE clause and should be used when checking for empty strings or specific values for other data types.