SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SP: Best way filter data on big table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

maxsoft
Starting Member

Italy
5 Posts

Posted - 09/10/2013 :  06:39:43  Show Profile  Reply with Quote
(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

India
169 Posts

Posted - 09/10/2013 :  07:56:59  Show Profile  Reply with Quote
SELECT * FROM Orders
WHERE Orders.State = @State
and Orders.CustomerCode = @CustomerCode
and Orders.ZipCode = @ZipCode

veeranjaneyulu
Go to Top of Page

maxsoft
Starting Member

Italy
5 Posts

Posted - 09/10/2013 :  08:59:35  Show Profile  Reply with Quote
...
VeeranjaneyuluAnnapureddy thank you for your reply but,

I mean multiple optional filter.
nobody know ?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3706 Posts

Posted - 09/10/2013 :  09:07:40  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000