Write sql to search with multiple conditions

Question

I have a Student table with two fields:

Name: nvarchar(256)
Age: int

The user will use the WinForm application to enter the name and age for the search.

• If the entered Name is empty, sql will not request the Name field.
• If the entered age is 0, sql will not request the Age field.
• If the name is Null and the name entered is empty → the entry matches. If the name is Null and the entered name is not empty → the record is not mapped. And also for the field "Age".

My problem is how to write sql like this.

P / S: I am using SQL Server 2005.

Please help me. Thank.

Answer 1

, :

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

[: sniffing , ...]

Answer 2

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

. ( , ), sql. , Linq to SQL SQL , :

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

SQL, WHERE .

. . :

• Configuring sql statement in input-based function
• PHP: prepared statement, IF statement required

These questions concerned PHP, but the technique can be adapted to any programming language.