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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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
did you go over ? – Remus Rusanu Nov 11 '13 at 20:40

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
@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

Your Answer


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.