Dynamic WHERE clause in SQL Sproc

For the last couple of days I've been working on a current website project converting SQL strings into Sprocs (Stored Procedures) to improve security, performance and maintainability, the database in question is SQL Server 2000.

A couple of the SQL Queries to be converted where dynamic and generated on the fly using the search criteria provided by the user.

In an ideal world you try to avoid having one Sproc that generates a dynamic query for performance reasons, though the hit on performance would be dependant upon the complexity of the query.

Therefore, you try to break it down in several Sprocs and use some conditional statements in your server-side code to determine which Sproc to call dependant upon the search criteria provided by the user. However, there are times when you still need to create a Sproc with one or more optional parameters, which will in turn determine the filters to be used in the WHERE clause.

This was the exact scenario I faced today, I had a Sproc that accepted an optional parameter. If the optional parameter was not Null an extra filter was required on the WHERE clause.

I stumbled across a great tutorial on Preventing dynamic SQL when using dynamic WHERE clause. This method lets you apply filters in the WHERE clause without the need for IF...ELSE or CASE statements. The end result is Sproc that is easy to read and wasnt bloated.

Example:

CREATE PROCEDURE spDynamicWhereSproc
  @OptionalParam varchar(10) = Null
AS
  SELECT * FROM tbl_SomeTable
  WHERE
        (@OptionalParam Is Null OR tbl_SomeTable.fld_Field = @OptionalParam)

If @OptionalParam is not passed it is NULL by default. If @OptionalParam is NULL the right side of the OR is not evaluated, meaning this part of the WHERE clause has no effect since it evaluates to True.

The downside to the above method is a reduction in performance compared to other methods, as this method will not make use of indexes properly. The most efficient way (atleast known to me) would be to use a controlling procedure such as multiple IF...ELSE statements to execute a specific query dependant upon the parameter(s) provided, though this would lead to a bloated Sproc.

Anyhow, I was so impressed I felt compelled to tell you all!

I should state that Im no expert on SQL Server, though I did buy a great book called Professional SQL Server 2000 Programming (ISBN: 0-7645-4379-2) which I'd recommend to anyone who needs to work with this specific edition of SQL Server.