Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need to write many SP that have many optional filtering parameters. After reading Turgay Sahtiyan's (Senior SQL Server Premier Field Engineer at Microsoft) blog he mentions exactly the same problem and the way to solve it is through dynamic SQL. Found it in his slides http://blogs.msdn.com/b/turgays/archive/2013/09/07/sqlsaturday-243-cape-town-top-10-performance-tips-for-sql-server-developers.aspx

So my questions is it the only and the best approach or are there other options?

WHERE (@EmployeeFirstName IS NULL OR emp.First_Name LIKE @EmployeeFirstName )
AND (@EmployeeLastName IS NULL OR emp.Last_Name LIKE @EmployeeLastName )
AND (@EmployeeNetworkLogOn IS NULL OR emp.Network_ID LIKE @EmployeeNetworkLogOn )
AND (@EmployeeStatus IS NULL OR emp.Employee_Status = @EmployeeStatus )
AND (@EmployeeType IS NULL OR emp.Employee_Type = @EmployeeType )
AND (@SupervisorFirstName IS NULL OR sup.First_Name LIKE @SupervisorFirstName )
AND (@SupervisorLastName IS NULL OR sup.Last_Name LIKE @SupervisorLastName )
AND (@SupervisorNetworkLogOn IS NULL OR sup.Network_ID LIKE @SupervisorNetworkLogOn) 
share|improve this question
7  
did you go over sommarskog.se/dyn-search-2008.html ? –  Remus Rusanu Nov 11 '13 at 20:40
    
Huge +1 to Remus' comment. That is where I'd send you. Saved my butt many a time. –  Mike Walsh Nov 11 '13 at 21:58
    
You are aware it is 2013? Microsoft has for a long time implemented LINQ in variosu fashions - so unless there are reasons to go with filter SP's or string manipulation, the main languages for accessing data these days should have a compiler integrated sql generator available. The blog you refer to is - ignorant of MS own technologies in this area. –  TomTom Nov 12 '13 at 6:22
    
@TomTom I'm not sure how LINQ is suppose to solve this problem. LINQ is designed to make it more efficient to return data when it is called from application. One way or the other LINQ will need to be translated into SQL and it is not always optimal translation. I'm not against LINQ, but if company decided to use SP I can't make rewrite application. –  Vladimir Oselsky Nov 13 '13 at 13:20
    
Ah, no. But with LINQ you can dynamically add multiple where clauses and it still generates efficient SQL. Really. Try it. –  TomTom Nov 13 '13 at 14:20

1 Answer 1

Going to be pretty tough to get consensus on what "best" means, since there is a trade-off with dynamic SQL (you gain some plan stability for each version of the query, depending on parameters, but you lose things like readability, IntelliSense, etc). I've used the dynamic SQL route in many implementations and I highly recommend it.

Since you will be sending many varying queries to the server, it is best used in combination with the Optimize for Ad Hoc Workloads server setting. This prevents any plan from being fully cached and taking up valuable memory until the same query text has been submitted twice. And if you are seeing wide deltas in performance due to parameter sniffing, you may consider adding OPTION (RECOMPILE) to the query in all cases anyway (or at least in the cases that end up with huge plan skews). For example, you probably won't see any skew when the parameter is the PK and the query ends up being an exact match using a seek. But you might see greater skew when a datetime range is being used, or an exact match to a column with its own asymmetrical skew, and particularly when stats are not being religiously maintained (or no indexes support those specific queries).

share|improve this answer
    
what about Dynamic SQL vs CLR Stored Procedure? –  Vladimir Oselsky Nov 11 '13 at 21:31
3  
@SaUce That's like asking Toyota pick-up vs. Cannondale mountain bike. They're used to solve different problems and, typically, CLR is not very good at data access. You might want to use it to help construct your SQL string, but I don't think you'll get a whole lot of benefit out of abstracting that part of the logic. –  Aaron Bertrand Nov 11 '13 at 21:32
    
Best is sdynamic sql on this point. Otherwise the "with recombile" option comes to my mind - and that pretty much is it, as SP's can not dynamically change their code. utting together dynamic SQL also works, but many optional parameters is where SP's are falling apart. –  TomTom Nov 13 '13 at 14:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.