Dynamic Where clause in Sql server

Using COALESCE

/* 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()

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.

Using ISNULL

/* 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

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

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.

 

Using CASE

/* 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.

 Alternate

/* 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