sql where clause in select statement issue

Question

Hello everyone,

I am using SQL Server 2008 Enterprise with Windows Server 2008 Enterprise. I have a database table called "Book", which has many columns and three columns are important in this question, they are

• Author, varchar;
• Country, varchar;
• Domain, varchar.

I want to write a store procedure with the following logics, but I do not know how to write (because of complex query conditions), appreciate if anyone could write a sample for me?

Input parameter: p_author as varchar, p_country as varchar, and p_domain as varchar

Query conditions:

1. if p_author is specified from input, then any row whose Author column LIKE %p_author% is satisfied with condition, if p_author is not specified from input every row is satisfied with this condition;
2. if p_country is specified from input, then any row whose Country column = p_country is satisfied with condition, if p_country is not specified from input every row is satisfied with this condition;
3. if p_domain is specified from input, then any row whose Domain column LIKE %p_domain% is satisfied, if p_domain is not specified from input every row is satisfied with this condition;

The results I want to return (must met with all following conditions):

• records met with either condition 1 or 2;
• records must meet with condition 3;
• return distinct rows.

For example, records which met with condition 1 and condition 3 are ok to return, and records which met with condition 2 and condition 3 are ok to return.

thanks in advance, George

Answer 1

If I understand correctly, the following should work:

 SELECT * 
FROM Books
WHERE (
   ((Author LIKE '%' + @p_author + '%' OR @p_author = '') OR 
   (Country LIKE '%' + @p_country + '%' OR @p_country = ''))
   AND (@p_author <> '' OR @p_country <> '')
) AND 
(Domain LIKE '%' + @p_domain + '%' OR '%' @p_domain = '')

Answer 2

Dynamically changing searches based on the given parameters is a complicated subject and doing it one way over another, even with only a very slight difference, can have massive performance implications. The key is to use an index, ignore compact code, ignore worrying about repeating code, you must make a good query execution plan (use an index).

Read this and consider all the methods. Your best method will depend on your parameters, your data, your schema, and your actual usage:

Dynamic Search Conditions in T-SQL by by Erland Sommarskog

The Curse and Blessings of Dynamic SQL by Erland Sommarskog

If you have the proper SQL Server 2008 version (SQL 2008 SP1 CU5 (10.0.2746) and later), you can use this little trick to actually use an index:

There isn't much you can do since you are using LIKE, but if you were using equality, you could add OPTION (RECOMPILE) onto your query, see Erland's article, and SQL Server will resolve the OR from within (Column = @Param+'%' OR @Param='') AND ... before the query plan is created based on the run-time values of the local variables, and an index can be used if you weren't using LIKE.