SQL Book

Creating Dynamic SQL statements that reflect user form selections

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.



Tell a friend about this page