Today I used a little tidbit that I thought I would pass along. Note that although I’m specifically using SQL Server in these examples the ideas should work regardless. Most often I see two different approaches to using dynamic WHERE clauses in stored procedures:

1. Separate Queries The first method I see a lot is just setting up separate queries, and you end up seeing code that looks something like this:

IF @p_CompanyID IS NOT NULL AND @p_Name IS NOT NULL BEGIN
  SELECT * FROM Company WHERE CompanyID = @p_CompanyID AND Name = @p_Name
END ELSE BEGIN
IF @p_CompanyID IS NOT NULL BEGIN
  SELECT * FROM Company WHERE CompanyID = @p_CompanyID
END ELSE BEGIN
IF @p_Name IS NOT NULL BEGIN
  SELECT * FROM Company WHERE Name = @p_Name
END ELSE BEGIN
  SELECT * FROM Company
END
END
END

This gets ugly really quick.

2. Dynamic SQL – In this approach a query is built up from the parameters and an sp_executesql is used to dynamically execute the query. This can be a very bad approach, especially if the parameters being passed in are generated from some form of user search form input. This is the classic SQL Injection problem.

DECLARE @p_SQL AS NVARCHAR(3500)
SET @p_SQL = 'SELECT * FROM [Company] WHERE 1 = 1'
IF @p_CompanyID IS NOT NULL BEGIN
  SET @p_SQL = @p_SQL + ' AND [CompanyID] = ' + @p_CompanyID
END
IF @p_Name IS NOT NULL BEGIN
  SET @p_SQL = @p_SQL + ' AND [Name] = ''' + @p_Name + ''
END

exec sp_executesql @p_SQL

This is a lot cleaner than number 1, but it has problems. Also notice the use of the WHERE 1 = 1 statement. That's a nice little trick to keep you from having to check if you need the AND statement or not.

3. And the Winner Is – Finally, here's a much better approach, is clean and doesn't have the sql injection problems:

SELECT * FROM Company
WHERE
  ((@p_CompanyID IS NULL) OR [CompanyID] = @p_CompanyID)
  AND ((@p_Name IS NULL) OR [Name] = @p_Name)
  AND ((@p_City IS NULL) OR [City] = @p_City)
  AND ((@p_State IS NULL) OR [State] = @p_State)
  AND ((@p_Zip IS NULL) OR [Zip] = @p_Zip)

It takes a minute to get your head around what's going on, but basically we're saying give me this row if the parameter is not filtered out or the parameter equals the record we're looking for. You can build this up as I have done here, and keeps things clean. You can see how a simple search form could be implemented as shown above.