Today I am going to deal with a fairly common problem. You have a search form with a number of n fields, of which most or even all are optional. That means in the worst case there are n² (empty or not) possible combinations. Classic static SQL seems to be a problem, so the most obvious solution would be a SQL string that is dynamically generated within your program code. But since you rather would want to keep SQL inside the database (preferably in a stored procedure), this can't be the best solution. I would like to propose an alternative I came up with and which has proven to be quite handy.

So let's have a look at a test program I created for this problem:

It is a simple product search form. The products (with three attributes name, price and year) are stored in a SQL Server table. You can search for a product name, a price (lower and upper bound) and a year. These attributes have been selected due to their exemplary character. This example is obviously constructed since in real life search forms you will find a larger number of fields.

The following stored procedure demonstrates a flexible way to deal with a number of unknown search parameters.

CREATE PROCEDURE [dbo].[sp_Products_Search] 
	@Name nvarchar(MAX) = null,
	@PriceLBound money = null,
	@PriceUBound money = null,
	@Year int = null
AS
BEGIN
	SET NOCOUNT ON;

	SELECT *
	FROM dbo.Products 
	WHERE ((@Name IS null) OR (Name LIKE '%' + @Name + '%' ))
	AND   ((@PriceLBound IS null) OR (Price >= @PriceLBound ))
	AND   ((@PriceUBound IS null) OR (Price <= @PriceUBound ))
	AND	  ((@Year IS null) OR (Year = @Year ))
END

By default the parameters are null with the result that they are ignored by the SELECT statement. If no parameter is passed at all to this stored procedure, it will return all products. For large table it might be necessary to limit the amount of returned rows.

Last but not least I have included the source code that creates the stored procedure's binding. As you can see it requires a logic for each parameter which determines when to add it to the stored procedure.

Private Function CreateParameter(ByVal Name As String, ByVal dbType As SqlDbType, ByVal Value As Object) As SqlParameter
    Dim result As New SqlParameter(Name, dbType)

    result.Value = Value

    Return result
End Function

Private Function CreateStoredProcedure(ByVal Connection As SqlConnection) As SqlCommand
    Dim result As New SqlCommand("dbo.sp_Products_Search", Connection)

    result.CommandType = CommandType.StoredProcedure

    If Not Me.txtName.Text.Trim.Equals(String.Empty) Then
        result.Parameters.Add(CreateParameter("Name", SqlDbType.NVarChar, Me.txtName.Text))
    End If

    If Not Me.txtPriceLBound.Text.Trim.Equals(String.Empty) Then
        result.Parameters.Add(CreateParameter("PriceLBound", SqlDbType.Money, Decimal.Parse(Me.txtPriceLBound.Text)))
    End If

    If Not Me.txtPriceUBound.Text.Trim.Equals(String.Empty) Then
        result.Parameters.Add(CreateParameter("PriceUBound", SqlDbType.Money, Decimal.Parse(Me.txtPriceUBound.Text)))
    End If

    If Not (Me.cmbYear.Text.Trim.Equals("All") Or Me.cmbYear.Text.Trim.Equals(String.Empty)) Then
        result.Parameters.Add(CreateParameter("Year", SqlDbType.Int, Integer.Parse(Me.cmbYear.Text)))
    End If

    Return result
End Function

Full source code (including database script) can be found here.