Dynamic Where Clause

Dynamic Where Clause

Some times we may encounter situations like getting the data from data base with numerous parameters which become optional based on the conditions. For example ,

Case 1. Get All The Employees in a specific department.

Case 2. Get All The Employees in a specific department and whose salary is >= 30,000 $.

Case 3. Get All The Employees Whose Joining Date is >= ’01/01/2011′ .

etc….

In the above cases conditions are becoming optional from case to case. The quick solution to get data from the database is to write inline query which changes according to the each case. Like, appending the conditions to the inline query as bellow.

Ex :-

public DataTable GetEmployees(long aDeptId = 0,DateTime? aJoinDate,decimal aSalary=0.0)

{

string strQuery = " SELECT * FROM M_EMPLOYEE WHERE 1=1 ";

 

if(aDeptId != 0)

{

strQuery +=" AND DEPTID =" + aDeptId;

}

if( aJoinDate != null)

{

strQuery += " AND DATEDIFF(dd,JOINDATE,'"+aJoinDate+"') >=0 ";

}

if(aSalary != 0.0)

{

strQuery += " AND SALARY >="+aSalary;

}

 

try

{

return DataLayer.Execute(strQuery);

}

catch(Exception ex)

{

throw ex;

}

}

This is simple. But when ever this query goes to Database, the Database Engine first complie the Query then execute. This process will be done every time when we search the employees by using above method. In order to skip the compilation of query at Database every time, we can write stored procedure for the above purpose using OPTIONAL PARAMETERS as below.

Ex:-

 CREATE PROCEDURE GetEmployess

(

      @DeptId bigint = null,

      @JoinDate datetime = null,

      @Salary money = null,

 

)

AS

 

DECLARE @Query VARCHAR(100);

DECLARE @ParamDefinition NVARCHAR(2000);

 

SET @Query = ' SELECT * FROM M_EMPLOYEE WHERE 1=1';

 

IF @DeptId IS NOT NULL

         SET @Query = @Query + ' AND DEPTID = @DeptId ';

 

IF @JoinDate IS NOT NULL

         SET @Query = @Query + ' AND DATEDIFF(dd,JOINDATE, @JoinDate ) >= 0';

 

IF @Salary IS NOT NULL

 

         SET @Query = @Query + ' AND SALARY >= @Salary';

 

// Set the Order of the parameter which are included in Query Statement.

SET @ParamDefinition = ' @DeptId BIGINT, @JoinDate DateTime,@Salary Money';

 

// Execute the Query Using Built In Stored Proc

Execute sp_ExecuteSql @Query, @ParamDefinition,@DeptId,@JoinDate,@Salary.

 

GO

Note : - sp_ExecuteSql  is a built in stored procedure which executes query string with order of the parameters using @ParamDefinition. 

This is bit complex than previous method and works same as like that. Here we have shifted the query construction to database from the application. Though we wrote the logic inside a Stored procedure, still we didnt skip the compilation process which happens every time when ever we call this stored procedure. No worries :). We can skip that by using following method.

Ex:-

CREATE PROCEDURE GetEmployess

(

@DeptId bigint = null,

@JoinDate datetime = null,

@Salary money = null,

 

)

AS

SELECT * FROM M_EMPLOYEE WHERE DEPTID = ISNULL(@DeptId,DEPTID)

AND JOINDATE >= ISNULL(@JoinDate,JOINDATE) AND SALARY >= ISNULL(@Salary,SALARY).

Simple right? By doing this we can skip the repeated compilation process and get rid of the multiple if else checking.