SP: Best way filter data on big table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SP: Best way filter data on big table

Author

Topic

maxsoft
Starting Member

5 Posts

Posted - 2013-09-10 : 06:39:43

(Performance issue)
Hi all.
I've a stored procedure that retrive data from Orders table.
User can select different fileter.
e.g. State, CustomerCode, ZipCode.
I wrote the SP like this:
<sql>
...
@State varchar(10) = null
,@CustomerCode varchar(10) = null
,@ZipCode varchar(10) = null
...
select * from Orders
where
(Orders.State = @State or @State is null)
and (Orders.CustomerCode = @CustomerCode or @CustomerCode is null)
and (Orders.ZipCode = @ZipCode or @ZipCode is null)
</sql>

is it the best way to accept and retrive data based on different filter?

thank you.

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-09-10 : 07:56:59

SELECT * FROM Orders
WHERE Orders.State = @State
and Orders.CustomerCode = @CustomerCode
and Orders.ZipCode = @ZipCode

veeranjaneyulu

maxsoft
Starting Member

5 Posts

Posted - 2013-09-10 : 08:59:35

...
VeeranjaneyuluAnnapureddy thank you for your reply but,

I mean multiple optional filter.
nobody know ?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-10 : 09:07:40

quote:
Originally posted by maxsoft

(Performance issue)
Hi all.
I've a stored procedure that retrive data from Orders table.
User can select different fileter.
e.g. State, CustomerCode, ZipCode.
I wrote the SP like this:
<sql>
...
@State varchar(10) = null
,@CustomerCode varchar(10) = null
,@ZipCode varchar(10) = null
...
select * from Orders
where
(Orders.State = @State or @State is null)
and (Orders.CustomerCode = @CustomerCode or @CustomerCode is null)
and (Orders.ZipCode = @ZipCode or @ZipCode is null)
</sql>

is it the best way to accept and retrive data based on different filter?

thank you.

While in theory, this should work, it can result in some very poor query plans due to parameter sniffing. This is one of those cases where dynamic SQL would help. For the correct way to use dynamic SQL for this problem without exposing your system to SQL injection attacks, see this blog: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Also, this article is very good: http://www.sommarskog.se/dyn-search-2008.html

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources