I feel as though I've written a thousand search forms where a user is presented with a form and then can pick from a list of fields in a form to perform a search against. In the past I've always just build a dynamic WHERE clause looking for a value in each field of the form and then appending that value(s) to the SQL statement i.e.:
Dim strReturnTrend As String = ddlReturnTrend.SelectedValue
If strReturnTrend <> "" Then
If blnNotFirst = True Then SqlText += " AND "
SqlText += " ReturnTrend= '" & strReturnTrend & "'"
blnNotFirst = True
End If
You can avoid this lengthy process of building a dynamic WHERE clause by using default values and optional parameters in your stored procedures.
create procedure [dbo].[sp_search_directors]
@Company varchar(50) = null,
@DirectorFirstName varchar(50) = '',
@DirectorLastName varchar(50) = '',
@Address varchar(50) = ''
@Title varchar(50) = ''
AS
SELECT
Company,
Director_FirstName,
Director_LastName,
Address,
Title
FROM
TDirectors
WHERE
Company = IsNull(@Company, Company),
and Director_FirstName LIKE
case @DirectorFirstName when '' then Director_FirstName
else @DirectorFirstName +'%' end
and Director_LastName LIKE
case @DirectorLastName when '' then Director_LastName
else @DirectorLastName +'%' end
and Address like
case @Address when '' then Address
else @Address + '%' end
and Title like
case @Title when '' then Title
else '%' + @Title + '%' end
This single query can search for directors by any combination of the parameters. The conditions in the where clause are such that when the default value is used, the condition will always be true. So to search for directors by a title starting with 'Big Money' the call would look like the following:
sp_search_directors null,'','','','Big Money'
The conditional logic contains a couple of different ways to do this and they are both equivalent.
First, if the default value of a parameter is null then the IsNull construct of the first condition is a simple way to implement this.
In the second example, the case statement is used to test for the default value and simply subsitute the field value in place of the parameter value, if the parameter value is something other than the default, then the parameter value is used instead.
For the directors name and address, the condition is a "starts with" match. For the title the condition is a "contains" match.
One more thing to remember, in SQL, a null = null is not a match. So if the data columns are allowed to have null values, we need to use IsNull to supply a dummy value for the match and in that case the syntax for the condition will be as follows:
and IsNull(Director_FirstName,'') like
case @DirectorFirstName when '' then IsNull(Director_FirstName,'')
else @DirectorFirstName +'%' end
Not everyone has a first name so we allow that field to be null. If the value of the director field is null, it will be converted to an empty string so instead of null = null which does not match, we now have ''='' ( 'empty string' = 'empty string' ) which does match.