Stored Procedure...help with searching data
Posted on 2005-05-02
I have a stored procedure that I am executing for an .asp page to allow a user to search for a record. The user provides any OR all of following the parameters (they should not have to enter data into every field); Parcel Number, First Name, Last Name, House Number and/or Street Name. The stored procedure was working great until I started adding in more parameters, and now it is not returning the data I want. The Parcel Number and House Number fields should be an exact match (find exactly that record, not 'LIKE'), but the other fields should be 'Like', and find anything similar. If I change the 'and' to 'or' in the last part of the sp I feel like I am getting closer, but can't get it quite right...in some cases I am getting too much data and in other cases nothing is being returned.
This is my stored procedure:
CREATE PROCEDURE ExExSearch
@strPIN varchar (10),
SET NOCOUNT ON
Select @strFirstName = '%' + @strFirstName +'%' ,
@strLastName = '%' + @strLastName +'%' ,
@strStreet = '%' + @strStreet +'%'
Where parcel_number = @strPIN
and first_name LIKE @strFirstName
and last_name LIKE @strLastName
and housenum LIKE @strHouseNum
and street LIKE @strStreet