SQL Tips by Namwar Rizvi

June 14, 2007

Dynamic Date Search without writing dynamic SQL

Filed under: Information,Performance,Query,Search columns,tips,TSQL,Tuning — namwar @ 7:29 PM

Many of us sometimes got stuck when our application requires flexible search mechanism where one or all of the search parameters can be NULL. These type of queries requires you to implement a mechanism which takes care of all possible combinations of input to the stored procedure.
Following is a quick,simple and efficient way of implementing flexible date search. Technique used here is applicable for any type of parameter.

We will create a stored procedure which will take two parameters FromDate and ToDate. Both, any or none of these parameters can be Null. I am using AdventureWorks sample database and the table we are searching on is Sales.SalesOrderHeader. This table contains a column called OrderDate. The objective here is to return
1. all orders whose OrderDate falls in the given range if both From and To Dates are provided.
2. all orders whose OrderDate is greater than or equal to the given FromDate if ToDate is NULL.
3. all orders whose OrderDate is less than or equal to the given ToDate if FromDate is NULL.
4. and finally all orders if both From and To Dates are Null.

Following is the stored procedure:

Use AdventureWorks
Go

Create proc usp_GetSalesOrderHeaderInfo
(
@p_FromDate datetime,
@p_ToDate datetime

)
as
Begin

–Set date format to Day/Month/Year
Set dateformat ‘dmy’

Select * from Sales.SalesOrderHeader
Where OrderDate between
–If respective parameter is Null then OrderDate is equal to itself which is always true
Isnull(@p_FromDate,OrderDate) and isnull(@p_ToDate,OrderDate)

End

and Following are some sample calls to the above stored procedure

–Set date format to Day/Month/Year
Set dateformat ‘dmy’

—Get all orders of one day i.e. 17-March-2004
exec usp_GetSalesOrderHeaderInfo ’17/03/2004′,’17/03/2004′

—Get all order uptill 17-March-2004
exec usp_GetSalesOrderHeaderInfo NULL,’17/03/2004′

—Get all order from 17-March-2004 and after
exec usp_GetSalesOrderHeaderInfo ’17/03/2004′,NULL

—Get all orders
exec usp_GetSalesOrderHeaderInfo NULL,NULL

How it works?
The crux of the logic is If the parameter is Null then compare the value with itself which will always results TRUE so practically you have removed the where clause condition for that parameter without creating dynamic SQL for different combinations.

Advertisements

1 Comment »

  1. I have been trying desparately for a solution for the following problem:

    The date column in the database can be Either NULL or a VALUE. I want to solve (without the need of using dynamic SQL) the following objective:

    (a) If the user wants he can select a DATE RANGE
    (b) If the user Wants he can select ALL the Dates
    (c) If the user wants he can select only NULL VALUES.

    A little extention of the problem that you have solved above…..
    please help!

    Comment by Vinay — April 3, 2008 @ 5:34 AM | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: