Click here to Skip to main content
15,867,453 members
Articles / Database Development / SQL Server

Implementing Dynamic WHERE-Clause in Static SQL

Rate me:
Please Sign up or sign in to vote.
4.68/5 (35 votes)
8 Nov 2007CPOL5 min read 649.1K   546   67   16
This article shows different methods of implementing Dynamic WHERE-Clause in static SQL.

Contents

Introduction

My previous article was about Building Dynamic SQL In a Stored Procedure. I explained how to write and execute Dynamic SQL using sp_executesql command. Well, when we take a look at the query execution plan of these two methods (static and dynamic); there is a huge difference in the speed and performance where as the static SQL is checked for syntax error, parsed, compiled and the execution plan is stored in the SQL Server's cache for subsequent execution. As I mentioned in my previous article as a special note, Dynamic SQL Queries in a variable are not compiled, parsed, checked for errors until they are executed. It is impossible for the SQL Server to reuse the execution plans produced by the dynamic SQL statement. When performance is the top priority, then one should avoid using Dynamic SQL statement. Speed, performance and reusability are the known issues with Dynamic SQL. Here I would like to show few ways of Implementing Dynamic WHERE-Clause in Static SQL. I take pleasure in writing this article which would force you to think of a better alternative rather jumping-in to write Dynamic SQL. And to those who read this, I suggest you to have a look at my previous article for better understanding. It's always an advantage to know the details of both the methods which will help you to choose a more efficient way.

Implementing Dynamic WHERE-Clause

Using COALESCE

The COALESCE Function in SQL Server serves a better purpose in handling columns with NULL values. It helps us to take a more efficient approach in building dynamic WHERE-clause. Before we get into an example, Let me explain how this "COALESCE Function" works.

Basic Syntax: COALESCE()

SQL
COALESCE ( expression1 , expression2, expression3....n) 

COALESCE function accepts multiple expressions as arguments and processes the expression list from left to right. This function returns the first Non-Null expression in the expression list. If all arguments are NULL, it returns NULL.

Note: All expressions must be of the same type or must be implicitly convertible to the same type.

Let us take an example - Employee table with common fields and wrap the statements in a stored procedure. Following Transact-SQL CREATE TABLE statement is to create an Employees table within your database.

SQL
/* Transact-Sql to create the table tblEmployees */
CREATE TABLE tblEmployees
(
    EmployeeID       SMALLINT IDENTITY(1001,1) NOT NULL,
    EmployeeName     NVARCHAR(100) NOT NULL,
    Department       NVARCHAR(50) NOT NULL,
    Designation      NVARCHAR(50) NOT NULL,
    JoiningDate      DATETIME NOT NULL,
    Salary           DECIMAL(10,2) NOT NULL
)

The following INSERT statements insert some sample records into the tblEmployee table:

SQL
/* Transact SQL to insert some sample records into tblEmployees table */
INSERT INTO tblEmployees
(EmployeeName, Department, Designation,  JoiningDate, Salary) VALUES
('John Smith', 'IT Research', 'Research Analyst', '02/08/2005', 23000.00)

INSERT INTO 
tblEmployees(EmployeeName, Department, Designation, JoiningDate, Salary) 
VALUES('John Micheal', 'IT Operations', 'Manager', '07/15/2007', 15000.00)

INSERT INTO 
tblEmployees(EmployeeName, Department, Designation, JoiningDate, Salary) 
VALUES('Will Smith', 'IT Support', 'Manager', '05/20/2006', 13000.00)

The below written stored procedure (Example 2.1) can be used to search for the following details in the Employee (tblEmployees) table.

  • Search for specific Employee detail with the Name.
  • List of Employees in a specific Department.
  • List of Employees in a specific Designation.
  • List of Employees joined the organization last year.
  • List of Employees whose Salary >= some specific Amount.
  • Any of these conditions listed above or all of these.

The SELECT query in the stored procedure dynamically implements the WHERE-Clause using COALESCE function to get the desired result.

Example 2.1 - Using COALESCE

SQL
/* Create Stored Procedure 'sp_EmployeeSelect_Coalesce'. 
Example 2.1 - Using Coalesce */ 

Create Procedure sp_EmployeeSelect_Coalesce
    @EmployeeName NVarchar(100),
    @Department NVarchar(50),
    @Designation NVarchar(50),
    @StartDate DateTime,
    @EndDate DateTime,
    @Salary Decimal(10,2)
        
AS
      Set NoCount ON
 
    Select * From tblEmployees 
    where EmployeeName = Coalesce(@EmployeeName, EmployeeName) AND
        Department = Coalesce(@Department, Department ) AND
        Designation = Coalesce(@Designation, Designation) AND
        JoiningDate >= Coalesce(@StartDate, JoiningDate) AND 
        JoiningDate <= Coalesce(@EndDate, JoiningDate) AND
        Salary >= Coalesce(@Salary, Salary)

    If @@ERROR <> 0 GoTo ErrorHandler
    Set NoCount OFF
    Return(0)
  
ErrorHandler:
    Return(@@ERROR)
GO

In the above stored procedure, for each condition in the WHERE-clause, the COALESCE function picks the first non-null value and uses it for the comparison operation. If the input parameter value is NULL, then the coalesce function returns the actual value which equals itself. The causes the particular row to be returned for that operation.

Using ISNULL

ISNULL is a T-SQL System function used to handle NULL values, it takes two argument, the first one is to check for an expression and the second argument is for the replacement value if the check expression value is NULL. We can say that ISNULL is equivalent to COALESCE function with two arguments.

Basic Syntax : ISNULL()

SQL
ISNULL ( check_expression , replacement_value )

Note: replacement_value must have the same type as check_expresssion.

Let's take the above example and write the stored procedure that builds the WHERE-clause dynamically using the ISNULL function.

Example 2.2 - Using ISNULL

SQL
/* Create Stored Procedure 'sp_EmployeeSelect_ISNULL'. 
Example 2.2 - Using IsNull */ 

Create Procedure sp_EmployeeSelect_ISNULL
    @EmployeeName NVarchar(100),
    @Department NVarchar(50),
    @Designation NVarchar(50),
    @StartDate DateTime,
    @EndDate DateTime,
    @Salary Decimal(10,2)
AS
      Set NoCount ON
  
    Select * From tblEmployees 
    where EmployeeName = IsNull(@EmployeeName, EmployeeName) AND
        Department = IsNull(@Department, Department ) AND
        Designation = IsNull(@Designation, Designation) AND
        JoiningDate >= IsNull(@StartDate, JoiningDate) AND 
        JoiningDate <= IsNull(@EndDate, JoiningDate) AND
        Salary >= IsNull(@Salary, Salary)

    If @@ERROR <> 0 GoTo ErrorHandler
    Set NoCount OFF
    Return(0)
  
ErrorHandler:
    Return(@@ERROR)
GO 

You can see in example 2.2 - WHERE-clause is built dynamically using the ISNULL function. It evaluates the expression and checks whether the parameter value is NULL or not. When this check expression returns a Non-Null value, it uses the parameter value in the comparison operation. When the check expression returns null, it uses the current value which equals itself and that causes all the rows to be returned for that operation.

Using CASE

CASE function is equivalent to the COALESCE function in SQL Server. It evaluates a list of conditions and returns one result expression from multiple possible result expressions. There are two types of CASE functions:

  • Simple CASE
  • Searched CASE

Basic Syntax: Simple CASE

SQL
CASE input_expression
   WHEN (when_expression1 IS NOT NULL) THEN result_expression1
   WHEN (when_expression2 IS NOT NULL) THEN result_expression2
   ...
   WHEN (when_expressionN IS NOT NULL) THEN result_expressionN
   ELSE else_result_expression
END 

Basic Syntax: Searched CASE

SQL
CASE
   WHEN (boolean_expression1 IS NOT NULL) THEN result_expression1
   WHEN (boolean_expression2 IS NOT NULL) THEN result_expression2
   ...
   WHEN (boolean_expressionN IS NOT NULL) THEN result_expressionN
   ELSE else_result_expression
END

The simple CASE function compares the input_expression with the when_expression to get the desired result_expression. The searched CASE function evaluates a set of Boolean expression to get the desired result_expression. Let's take the same example and write the stored procedure that builds the WHERE-clause dynamically using the CASE function.

Example 2.3 - Using CASE

SQL
/* Create Stored Procedure 'sp_EmployeeSelect_Case'. 
Example 2.3 - Using Case-When */ 

Create Procedure sp_EmployeeSelect_Case
    @EmployeeName NVarchar(100),
    @Department NVarchar(50),
    @Designation NVarchar(50),
    @StartDate DateTime,
    @EndDate DateTime,
    @Salary Decimal(10,2)
AS
      Set NoCount ON
  
    Select * From tblEmployees where EmployeeName = 
    Case When @EmployeeName Is Not Null Then @EmployeeName 
    Else EmployeeName End AND Department = 
    Case When @Department Is Not Null Then @Department 
    Else Department End AND Designation = 
    Case When @Designation Is Not Null Then @Designation 
    Else Designation End AND JoiningDate >= 
    Case When @StartDate Is Not Null Then @StartDate 
    Else JoiningDate End AND JoiningDate <= 
    Case When @EndDate Is Not Null Then @EndDate 
    Else JoiningDate End AND Salary >= 
    Case When @Salary Is Not Null Then @Salary 
    Else Salary End 

    If @@ERROR <> 0 GoTo ErrorHandler
    Set NoCount OFF
    Return(0)
  
ErrorHandler:
    Return(@@ERROR)
GO

You can see in example 2.3 that WHERE-clause is built dynamically using the CASE function. It evaluates the expression and checks whether the parameter values are NULL or not. When this boolean expression returns true, it uses the parameter value in the comparison operation. When the boolean expression returns false, it uses the current value which equals itself and that causes all the rows to be returned for that operation.

Alternative

Here is an alternative suggested by a CodeProject member in the article discussion of my first article. This alternative uses neither COALESCE nor CASE function to build Dynamic WHERE-clause but a logic equivalent to it - worth using it.

Example 2.4 - Alternate

SQL
/* Create Stored Procedure 'sp_EmployeeSelect_Alternate'. 
Example 2.4 - Alternate */ 

Create Procedure sp_EmployeeSelect_Alternate
    @EmployeeName NVarchar(100),
    @Department NVarchar(50),
    @Designation NVarchar(50),
    @StartDate DateTime,
    @EndDate DateTime,
    @Salary Decimal(10,2)
AS 
    Set NoCount ON
  
    SELECT * FROM tblEmployees
    WHERE (@EmployeeName Is Null OR @EmployeeName = EmployeeName) AND
        (@Department Is Null OR @Department = Department) AND
        (@Designation Is Null OR @Designation = Designation) AND
        (@Salary Is Null OR @Salary = Salary) AND
        (@StartDate Is Null OR @EndDate Is Null OR 
        (@StartDate Is Not Null AND @EndDate Is Not Null AND 
        JoiningDate BETWEEN @StartDate AND @EndDate))

    If @@ERROR <> 0 GoTo ErrorHandler
    Set NoCount OFF
    Return(0)
  
ErrorHandler:
    Return(@@ERROR)
GO 

Conclusion

Hope you learned how to implement dynamic WHERE-Clause in static SQL. In all the examples, I showed how to handle the input parameter when it is NULL. Even if its a different Non-Null value to be used for multiple comparisons, you can still play around with simple CASE and searched CASE-functions more efficiently. Hence I conclude that static SQLs are faster, safer and in most cases, we don't need to use dynamic SQL-statement.

Reference

History

  • 8th November, 2007: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Ireland Ireland
Many years of experience in software design, development and architecture. Skilled in Microsoft .Net technology, Cloud computing, Solution Design, Software Architecture, Enterprise integration, Service Oriented and Microservices based Application Development. Currently, focusing on .Net Core, Web API, Microservices, Azure

Comments and Discussions

 
QuestionDynamically choosing a value from a group values based on the user input Pin
Member 137114956-Mar-18 1:50
Member 137114956-Mar-18 1:50 
GeneralMy vote of 2 Pin
Ehsan yazdani rad15-Oct-14 1:12
Ehsan yazdani rad15-Oct-14 1:12 
GeneralMy vote of 1 Pin
Member 99825083-Sep-13 2:23
Member 99825083-Sep-13 2:23 
GeneralRe: My vote of 1 Pin
John-ph17-Sep-13 2:41
John-ph17-Sep-13 2:41 
GeneralRe: My vote of 1 Pin
Neolisk25-Mar-15 7:56
Neolisk25-Mar-15 7:56 
GeneralMy vote of 5 Pin
Mohsen sayadian18-Aug-13 21:26
Mohsen sayadian18-Aug-13 21:26 
Questionprecision of "all values" Pin
PDTPGY7-Dec-12 4:05
PDTPGY7-Dec-12 4:05 
GeneralMy vote of 5 Pin
Tech Spec21-May-12 12:50
Tech Spec21-May-12 12:50 
QuestionStoredProcedure with search NULL Pin
VanhoveLuc4-May-10 23:43
VanhoveLuc4-May-10 23:43 
QuestionWhat about 1=1 ? Pin
zeego25-Mar-10 20:52
zeego25-Mar-10 20:52 
GeneralThe parameter side Pin
chrisjft115-Oct-08 18:19
chrisjft115-Oct-08 18:19 
QuestionSo which is the recommended style? Pin
tmpuserz16-Apr-08 10:42
tmpuserz16-Apr-08 10:42 
That was a good summary of some different styles. Although helpful it would be nice to have some guidance on which style to use and when. Which style do you recommend?
AnswerRe: So which is the recommended style? Pin
John-ph16-Apr-08 19:22
John-ph16-Apr-08 19:22 
Generalad Example 1.1 Pin
Tonki12-Nov-07 11:54
Tonki12-Nov-07 11:54 
GeneralRe: ad Example 1.1 Pin
John-ph12-Nov-07 19:37
John-ph12-Nov-07 19:37 
GeneralMassive use of ISNULL and other function Pin
Jcmorin9-Nov-07 14:56
Jcmorin9-Nov-07 14:56 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.