Suppose you want a search form for a user table in your database.
You can search on email, username and city, and for each you can put it in a seperate field.
So you can filter on username, email and city.

How to make your query for MS SQL Server 2000 ?
I tried this :

CREATE PROCEDURE searchUsers
@email NVARCHAR(40) default NULL,
@username NVARCHAR(100) default NULL,
@city NVARCHAR(100) default NULL

AS
BEGIN

SELECT * FROM users
WHERE email LIKE ‘%’ + @email + “%”
AND username LIKE ‘%’ + @username + “%”
AND city LIKE ‘%’ + @city + “%”

END;

Quite simple isn’t it ?

But what if one of the columns, for example ‘city’, contains NULL values.
If you search on email = ‘qtid@risk.com’ and city = ‘%’ (or ”)
it will only return those rows that have no NULL values for city.

Of course, one can say to not put the where clause of city, when no city is specified, but than how do I make a search form that can filter results by multiple options or fields ?

It is also considered as one of the great SQL Server time-wasting brainers.
And I’m not alone.