Wrox Programmer Forums
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 10th, 2007, 11:05 AM
Authorized User
 
Join Date: Oct 2006
Posts: 76
Thanks: 0
Thanked 0 Times in 0 Posts
Default search engine

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



 
Old April 10th, 2007, 11:13 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old April 10th, 2007, 12:46 PM
Friend of Wrox
 
Join Date: May 2006
Posts: 246
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Also put % after LIKE comparison variable...


 
Old April 10th, 2007, 08:51 PM
Friend of Wrox
 
Join Date: Aug 2004
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

 
Old April 11th, 2007, 12:42 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
search engine raaj Beginning PHP 1 August 1st, 2007 06:52 AM
Search Engine for Full-text Search Kala ASP.NET 1.0 and 1.1 Professional 2 August 29th, 2004 02:16 AM
Search engine that search through local drive! wenzation Classic ASP Basics 0 August 26th, 2003 09:15 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.