SQL Book

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.

-- Create a new table CREATE TABLE NewCustomers ( CustomerID int IDENTITY(1,1) NOT NULL, Forename nvarchar(30) NOT NULL, Surname nvarchar(30) NOT NULL ) -- Add a couple of records to the table INSERT INTO NewCustomers VALUES ('Joe', 'Bloggs') INSERT INTO NewCustomers VALUES ('Bob', 'Marley') -- Declare variables that will be used in dynamically -- building the SQL statement DECLARE @Forename nvarchar(30) DECLARE @Surname nvarchar(30) DECLARE @SQL nvarchar(1000) DECLARE @WHERE nvarchar(500) -- Set the variables (in the real world these values would -- most likely be passed to the database from a -- windows / web application). SET @Forename = NULL SET @Surname = 'Bloggs' -- Dynamically build the WHERE clause to reflect the -- values held in the filter criteria SET @SQL = 'SELECT * FROM NewCustomers WHERE ' IF NOT @Forename IS NULL Begin SET @WHERE = ' Forename = ''' + @Forename + '''' End IF NOT @Surname IS NULL Begin IF @WHERE <> '' Begin SET @WHERE = @WHERE + ' AND Surname = ''' + @Surname + '''' End ELSE Begin SET @WHERE = ' Surname = ''' + @Surname + '''' End End SET @SQL = @SQL + @WHERE -- Print the finished statement (very useful when trying to debug) PRINT @SQL -- Execute the SQL statement we have just built EXEC(@SQL)

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.

DECLARE @Var1 int, @Var2 int, @Var3 int SET @Var1 = NULL SET @Var2 = 100 SET @Var3 = 50 SELECT COALESCE(@Var1, @Var2, @Var3) As MyValue -- Results -- MyValue ----------- 100

To use the COALESCE function in our dynamic WHERE clause we can pass the COALESCE function:

  1. the variable that we are checking to see whether it holds a value
  2. 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:

-- The CREATE TABLE and INSERT statements have been -- omitted as table and dummy sample records already exist... DECLARE @Forename nvarchar(30) DECLARE @Surname nvarchar(30) SET @Forename = NULL SET @Surname = 'Bloggs' SELECT * FROM NewCustomers WHERE Forename = COALESCE(@Forename, Forename) AND Surname = COALESCE(@Surname, Surname)

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:

DECLARE @Forename nvarchar(30) DECLARE @Surname nvarchar(30) SET @Forename = '' SET @Surname = 'Bloggs' SELECT * FROM NewCustomers WHERE Forename = (CASE WHEN @Forename = '' THEN Forename ELSE @Forename END) AND Surname = (CASE WHEN @Surname = '' THEN Surname ELSE @Surname END)

We can even modify the CASE statements to also check for NULL values to cover all possibilities:

SELECT * FROM NewCustomers WHERE Forename = (CASE WHEN @Forename = '' THEN Forename WHEN @Forename IS NULL THEN Forename ELSE @Forename END) AND Surname = (CASE WHEN @Surname = '' THEN Surname WHEN @Surname IS NULL THEN Surname ELSE @Surname END)

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

WHERE CountryID = (CASE WHEN @CountryID = 0 THEN CountryID ELSE @CountryID END)

Conclusion

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.

Join our newsletter

Subscribe to our free weekly newsletter and get exclusive SQL tips and articles straight to your inbox. 

 Your email address is only used to send you our newsletter.