I'm not sure you should be enforcing the 'at least two conditions' criteria down in the database as you should probably never have knowledge of which two have been filled in. Perhaps instead this might work for you - it's a pattern I use quite often and should cope with any combination of criteria (I'm assuming this is inside a stored proc!):
DECLARE PROCEDURE PropertyList
@StreetName NVARCHAR(50) = NULL,
@Town NVARCHAR(50) = NULL,
@Postcode NVARCHAR(10) = NULL
AS
SET NOCOUNT ON
SELECT
*
FROM
VWTenantPropertiesResults
WHERE
ContentBedrooms BETWEEN 1 AND 4
AND
ContentPrice BETWEEN 50 AND 500
AND
(@ContentStreet IS NULL OR ContentStreet = @ContentStreet)
AND
(@ContentTown IS NULL OR ContentTown = @ContentTown)
AND
(@ContentPostcode IS NULL OR ContentTown = @ContentTown)
ORDER BY
ContentPrice
To call this from your ASP page you'll want some code something like this (this may need a bit of debugging, my ADO & VBScript for ASP is pretty rusty!):
Dim cnn 'As ADODB.Connection
Dim cmd 'As ADODB.Command
Dim prmStreet 'As ADODB.Parameter
Dim prmTown 'As ADODB.Parameter
Dim prmPostcode 'As ADODB.Parameter
Dim rstProperty 'As ADODB.RecordSet
Dim i 'As Integer
Set cnn = Server.CreateObject("ADODB.Connection")
cnn.ConnectionString = MyConnectionString
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cnn
'Set the CommandText property to the name of the stored proc we want to call
cmd.CommandText = "PropertyList"
cmd.CommandType = 4 'or adCmdStoredProc if you're using ADOVBS.inc
If Request.Form("StreetTextBox") = "" Then
'No street entered so don't pass it to the stored proc
Else
'A street has been entered so create a parameter...
Set prmStreet = cmd.CreateParameter("@StreetName", 203, 1, 50, Request.Form("StreetTextBox"))
' and add it to the Parameters collection of the Command object
cmd.Parameters.Add(prmStreet)
End If
If Request.Form("TownTextBox") = "" Then
'No town entered so don't pass it to the stored proc
Else
'A town has been entered so create a parameter...
Set prmTown = cmd.CreateParameter("@Town", 203, 1, 50, Request.Form("TownTextBox"))
' and add it to the Parameters collection of the Command object
cmd.Parameters.Add(prmTown)
End If
If Request.Form("PostcodeTextBox") = "" Then
'No postcode entered so don't pass it to the stored proc
Else
'A postcode has been entered so create a parameter...
Set prmPostcode = cmd.CreateParameter("@Postcode", 203, 1, 10, Request.Form("PostcodeTextBox"))
' and add it to the Parameters collection of the Command object
cmd.Parameters.Add(prmPostcode)
End If
cnn.Open
'This is the line that'll actually call the stored procedure
Set rstProperty = cmd.Execute()
cnn.Close
If rstProperty.BOF And rstProperty.EOF Then
'If BOF And EOF are true then this is an empty recordset - we got no records back
Response.Write "No records returned"
Else
'We have records so write them out into a table
Response.Write "<table><tr>"
For i = 0 To rstProperty.Fields.Count - 1
Response.Write "<td>"
Response.Write rstProperty.Fields(i).Name
Response.Write "</td>"
Response.Write "<td> </td>"
Next
Response.Write "</tr>"
Do While rstProperty.Eof = False
Response.Write "<tr>"
For i = 0 To rstProperty.Fields.Count - 1
Response.Write "<td>"
Response.Write rstProperty.Fields(i).Value
Response.Write "</td>"
Next
Response.Write "<td>"
Response.Write "<a href='ViewDetails.asp?id='" & rstProperty.Fields("PropertyId").Value & "'>View Details for this property</a>"
Response.Write "</td>"
Response.Write "</tr>"
rstProperty.MoveNext
Loop
Response.Write "</table>"
End If
This ought to work for any combination of parameters, whether you enter none, some or all of them!