SQL Server – Dynamic Where Clause

Things can be neatly done in application level by concatenating where clause however, there are times when you are out of that application and all you can do is SQL Query. I have found a good resource to do so but keep in mind that although it works, but it is a bit ugly.

The Problem

You are working on the database side of the project where all you can do is about the database and SQL queries. Then you are given a search module where there are input elements. Each input element is equivalent to a part of the where clause. Take the following example.

You have three fields: estimated_date, agent_cd and waste_type. estimated_date is required, whereas agent_cd and waste_type are optional. So here is the draft of the SQL Query:

use myappdb
go

declare @estimated_date as datetime;
declare @agent_cd as varchar(10);
declare @waste_type as varchar(1);

-------------- EDIT VARIABLES HERE--------------------

set @estimated_date = '2010/08/01';
set @agent_cd = 'BE01';
set @waste_type = '3';

-------------------------------------------------------

select
	convert(varchar(10), estimated_date, 111) as group_date,
	sum(weight) as total_weight
from
	[搬入実績]
where
	estimated_date >= @estimated_date and
	agent_cd = @agent_cd and
	weight > 0 and
	waste_type = @waste_type
group by
	estimated_date
order by
	estimated_date asc;

That was pretty easy. However, if agent_cd and waste_type is not entered, we’re screwed. Since for example if agent_cd is not entered, we should exclude the agent_cd from the where clause. How are we suppose to do that?

Don’t worry, there is a quick and dirty solution that should always work.

You can found it here: http://www.eggheadcafe.com/PrintSearchContent.asp?LINKID=469.

It is easy because it uses AND operator. You can effectively remove a certain where portion by equating a field to itself. Example agent_cd = agent_cd. So our final query will look like this:

use myappdb
go

declare @estimated_date as datetime;
declare @agent_cd as varchar(10);
declare @waste_type as varchar(1);

-------------- EDIT VARIABLES HERE--------------------

set @estimated_date = '2010/08/01';
set @agent_cd = 'BE01';
set @waste_type = '3';

-------------------------------------------------------

select
	convert(varchar(10), estimated_date, 111) as group_date,
	sum(weight) as total_weight
from
	[搬入実績]
where
	estimated_date >= @estimated_date and
	agent_cd = (case when DataLength(@agent_cd) > 0 then @agent_cd else agent_cd end) and
	weight > 0 and
	waste_type = (case when DataLength(@waste_type) > 0 then @waste_type else waste_type end)
group by
	estimated_date
order by
	estimated_date asc;

That way, it effectively excludes agent_cd and waste_type fields if they are empty.