Conditional WHERE Clauses in SQL Server 2008

I am trying to execute a query on a table in my SQL Server 2008 database. I have a stored procedure that uses five int parameters. Currently, my parameters are defined as follows:

@memberType int,
@color int,
@preference int,
@groupNumber int,
@departmentNumber int

This procedure will be passed -1 or higher for each parameter. A value of -1 m开发者_开发问答eans that the WHERE clause should not consider that parameter in the join/clause. If the value of the parameter is greater than -1, I need to consider the value in my WHERE clause. I would prefer to NOT use an IF-ELSE statement because it seems sloppy for this case.

I saw this question here. However, it did not work for me. I think the reason why is because each of the columns in my table can have a NULL value. Someone pointed this scenario out in the fifth answer. That appears to be happening to me.

Is there a slick approach to my question? Or do I just need to brute force it (I hope not :().

Thank you!

I've done something like this in the past:

SELECT
  .....
FROM
  dbo.SOMETABLE AS T
WHERE
  (T.memberType = @memberType OR @memberType = -1)
  AND (T.color = @color OR @color = -1)
  AND (T.preference = @preference OR @preference = -1)
  AND (T.groupNumber = @groupNumber OR @groupNumber = -1)
  AND (T.departmentNumber = @departmentNumber OR @departmentNumber = -1)

Generally, however, my parameters that I don't care about are NULL. Then the query becomes:

SELECT
    .....
  FROM
    dbo.SOMETABLE AS T
  WHERE
    (T.memberType = @memberType OR @memberType IS NULL)
    AND (T.color = @color OR @color IS NULL)
    AND (T.preference = @preference OR @preference IS NULL)
    AND (T.groupNumber = @groupNumber OR @groupNumber IS NULL)
    AND (T.departmentNumber = @departmentNumber OR @departmentNumber IS NULL)

I realize thread is older, but here are some additional details that may help in making an appropriate decision. There are multiple solutions:

1)

SELECT ...
FROM ...
WHERE 
  (T.memberType = @memberType OR @memberType = -1) 
  AND (T.color = @color OR @color = -1) 
  AND (T.preference = @preference OR @preference = -1) 
  AND (T.groupNumber = @groupNumber OR @groupNumber = -1) 
  AND (T.departmentNumber = @departmentNumber OR @departmentNumber = -1)

2)

SELECT ...
FROM ...
WHERE 
    (T.memberType = @memberType OR @memberType IS NULL) 
    AND (T.color = @color OR @color IS NULL) 
    AND (T.preference = @preference OR @preference IS NULL) 
    AND (T.groupNumber = @groupNumber OR @groupNumber IS NULL) 
    AND (T.departmentNumber = @departmentNumber OR @departmentNumber IS NULL) 

3) Dynamically generate the DML and use EXECUTE statement

4) Dynamically generate the DML and use sp_executesql

Options 1 and 2 are pretty much the same... I would tend to use IS NULL rather than -1, but as with most things, it depends on the situation. One of the disadvantages to these options is that the first execution of the stored procedure will produce a query plan that will be re-used in all subsequent calls... as the parameter's values change (specifically, which ones you want to ignore), the initial query plan may no longer be the optimal plan... to work around this problem, use the WITH RECOMPILE option (with the caveat that the procedure will be recompiled every time it is called).

Options 3 and 4 perform better as more data is added to the table(s) and/or more criteria are added to the WHERE clause. However, these options require more effort to write the stored procedure and require more validation of the input parameters to minimize potential SQL injection vulnerability. Option 4 is better than option 3 and is somewhat simpler in a sense because your dynamically-generated SQL contains the parameter names which leads to more effective query plan reuse. Another downside to dynamically-generated SQL is that the user calling the stored procedure must have all the necessary permissions on the underlying tables/views... unless the procedure is defined with the WITH EXECUTE AS ... clause.

In the end, I typically use dynamically generated SQL with sp_executesql to produce the best performing queries.

I would suggest dynamic sql - generate the query as a string (varchar) based on the parameters you receive. Add only the items to the where clause that you really need. Then use sp_executesql to run it. Dynamic sql is generally less efficient than pre-compiled sql, but in your case it sounds like the right way to go. Be sure to parameterize the query to give the optimizer the best shot at re-using query plans.