Write a sql for searching with multiple conditions

Question

I have a table Student with 2 fields:

Name: nvarchar(256)
Age: int

User will use a WinForm application to input a Name and a Age for searching.

• If inputted Name is empty, sql will not query Name field.
• If inputted Age is 0, sql will not query Age field.
• If Name is Null and inputted Name is empty -> record is matched. If Name is Null and inputted Name is not empty -> record is not matched. And also similar for Age field.

My problem is that how to write a sql like this.

P/S: I use SQL Server 2005.

Please help me. Thanks.

Answer 1

I would use dynamic SQL, building the WHERE clause with a variable number of terms based on the inputs.

I answered questions for very similar cases recently. See those answers for examples:

• Adjusting sql statement in a function based upon input
• PHP: prepared statement, IF statement help needed

Those questions were about PHP, but the technique can be adapted to any programming language.

Answer 2

select * from thetable
where (@name='' or [name]=@name) and (@age=0 or age=@age)

However, the above query forces table scan. For better performance and more complex scenario (I guess you simplified the question in you original post), consider use dynamic sql. By the way, Linq to SQL can help you build dynamic SQL very easily, like the following:

IQueryable<Person> persons = db.Persons;
if (!string.IsNullOrEmpty(name)) persons = persons.Where(p=>p.Name==name);
if (age != 0) persons = persons.Where(p=>p.Age=age);

Answer 3

One approach is to make the parameters nullable and use this pattern:

Create Procedure MyTest
(
    @param1 int = NULL,
    @param2 varchar = NULL 
)
AS
BEGIN

SELECT Blah...
FROM Table
WHERE
    ((@param1 IS NULL) OR (somecolumn = @param1)) AND
    ((@param2 IS NULL) OR (someothercolumn = @param2))


END

[Note: it can have parameter sniffing side effects, if there are many parameters...]