I have a table with customer details, and a front end
search form that allows the user to enter various search
criteria such as Surname, Forename, CompanyName, eMail
address etc.

Any combinations of search criteria are possible, and
some fields such as names allow wildcard searching, so
there are several permutations of the criteria to search
on.

In my current system I build up the SQL including the
appropriate where clause and pass that to SQLServer to
get the results.

So at runtime the query could look like:

SELECT * FROM CUSTOMER WHERE SURNAME LIKE 'SMI%' AND
COMPANYNAME LIKE 'BP' AND AGE=33

or sometimes just

SELECT * FROM CUSTOMER WHERE FORENAME = 'JOHN'


I am now moving all of my dataaccess to stored procedures
but am not sure how to do the above. I assume I should
have all of the possible search items passed into the SP
as parameters, but I don't know how to dynamically build
the where clause depending on which parameters have been
passed in.

I obviously don't want to execute a different SELECT
statement depending on which search parameters have been
supplied, as there will be many combinations.

Is there an easy way to do this type of thing?

Thanks!