I've having a small problem. I have a search engine that searches a database on sqlserver based on certain criteria. I would like to code the stored procedure so that the database can be searched with all, some, or none of the criteria and have only info for that criteria populate. This is what the code looks like now...

(
        @RecID varchar(15),
        @LName varchar(50),
        @Business varchar(100),
        @TaxID varchar(20),
        @Class varchar(5),
        @ChkNum varchar(25)
    )
AS
    Select App.vchFWRecID, App.vchLast, App.vchFirst, Bus.vchBusinessName, Bus.vchCity,
        Bus.vchFWClass, Bus.vchSalesID, Pay.vchCheckNum
    From tblFWApplicant App
    inner join tblFWBusiness Bus on App.vchFWRecID = Bus.vchFWRecID
    inner join tblFWPayment Pay on App.vchFWRecID = Pay.vchFWRecID
    where App.vchFWRecID like @RecID or App.vchlast like @LName or bus.vchFWClass like @Class
    or bus.vchBusinessName like @Business or bus.vchSalesID like @TaxID or pay.vchCheckNum like @ChkNum
    order by App.vchFirst asc

You might need to check if each parameter is NOT null and then apply that parameter's value to the criteria, only when it has some value passed.

Hope that helps.

_________________________
- Vijay G
Strive for Perfection

Also put % after LIKE comparison variable...

I think your missing the posters question/issue.

The way this is typically done is to build the "where" clause if people passed you a parameter. So for example you dim a @cmd string and make it a big one.
Set @Cmd = " select blah blah blah from blah inner join1 blah inner join blah2"
then add the where logic.
@cmd = @cmd + "where "
then conditionals
if App.vchFWRecID is not null
   begin
      @cmd = @cmd + App.vchFWRecID like @RecID
   end
if app @TaxID is not null
   begin
      @cmd = @cmd + or bus.vchSalesID like @TaxID
   end
-- after you build all the conditional where logic then execute the dynamicly built sql query
exec(@cmd)

-- but you have to build logic that works if no conditions exist and for example you would not have a where clause.

In fact the original poster is missing the % in his code posted. Since he uses like he needs to mention the pattern he wants to search. Good that Peso pointed out. I completely overlooked that.

_________________________
- Vijay G
Strive for Perfection