|
|
You're in the
sql server programming
group:Dynamic Date Search without writing dynamic SQL: Quick Tip!
sql server programming:
On Jun 14, 3:19 pm, "Namwar Rizvi" <namwarri...@yahoo.com> wrote: > 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. > > Cheers, > Namwar > See this and many other tips athttp://blog.namwarrizvi.com Your advice is a performance killer. I tried it out against a large table, and guess what: it scanned the whole table, although the range between two dates was very narrow (4 rows out of 2 million) and the existing index was potentially beneficial to put it very mildly.
[quoted text, click to view]
On Jun 14, 4:19 pm, "Namwar Rizvi" <namwarri...@yahoo.com> wrote: > 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. > > Cheers, > Namwar > See this and many other tips athttp://blog.namwarrizvi.com a function on the left side of an operator in the WHERE clause will almost always result in a table/index scan instead of a seek Instead of that, if null is passed in for startdate use 1753 (don't know the month and date on top of my head) min date and if the end date is null replace it with 9999-12-31 that way you CAN use the index Denis The SQL Menace http://sqlservercode.blogspot.com http://sqlblog.com/blogs/denis_gobo/default.aspx
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. Cheers, Namwar See this and many other tips at http://blog.namwarrizvi.com
Namwar Rizvi (namwarrizvi@yahoo.com) writes:
[quoted text, click to view] > Create proc usp_GetSalesOrderHeaderInfo > ( > @p_FromDate datetime, > @p_ToDate datetime > ) > as > Begin > --Set date format to Day/Month/Year > Set dateformat 'dmy' There is little reason to have this command in a stored procedure. As it can cause a recompile, it can have negative impact on performance. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
[quoted text, click to view]
> --Set date format to Day/Month/Year > Set dateformat 'dmy' WHY? [quoted text, click to view] > 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) I think you will find this much faster: WHERE OrderDate >= COALESCE(@p_FromDate, '17530101') AND OrderDate <= COALESCE(@p_ToDate, '99991231') And instead of horsing around with all of this silly d/m/y stuff when calling the stored procedure, try using ANSI standard formats, e.g. EXEC dbo.yourproc @param = '20070604'; This is not going to be mistaken for April 6th no matter what your language, regional settings, locale, dateformat, etc. -- Aaron Bertrand SQL Server MVP http://www.sqlblog.com/ http://www.aspfaq.com/5006
Hi Namwar Rizvi,
In case you haven't read them, here they are: Dynamic Search Conditions in T-SQL http://www.sommarskog.se/dyn-search.html The Curse and Blessings of Dynamic SQL http://www.sommarskog.se/dynamic_sql.html AMB [quoted text, click to view] "Namwar Rizvi" wrote:
> > 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. > > Cheers, > Namwar > See this and many other tips at > http://blog.namwarrizvi.com > >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |