search query. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

search query.

Author

Topic

terrence_chan99
Starting Member

12 Posts

Posted - 2006-05-29 : 03:27:54

I am writing a search query to search 3 fields in a table.

e.g. Broker table, has
BrokerId,
Firstname,
Lastname,
companyname

User can search by any number of the (Firstname, lastname or companyname) fields.

i.e. they can search ('Terrence', 'Chan', 'ABC Company')
OR
any of this combination

('Terrence', 'Chan', NULL)
('Terrence', NULL, 'ABC Company')
(NULL, 'Chan', 'ABC Company')
('Terrence', 'NULL', NULL)

or even
(null, null, null) -- return nothing.

How can I do that?

I know one way to do that is declare a @where_sql statement and build the query dynamically.

But it is a bit mesh, so I wonder is there any other way to do that?

Thanks,
Terrence

rihardh
Constraint Violating Yak Guru

307 Posts

Posted - 2006-05-29 : 03:57:53

There is a way and it's called GUI filtering. The best way to filter by user input is to use a clever gui control (like devx grid) which has such filter capabilities (multiple column filtering). If you stil want to filter in the database you won't get around complicated IF-AND-OR clauses with semi-dynamic input parameters.

HND

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-29 : 04:07:19

According to me you need to write a procedure like this ..


Create Proc sp_Search
(
@pFirstName Varchar(200) ='',
@pLastName Varchar(200)='',
@pCompanyName Varchar(200) = ''
)
As 
Begin 
  Select * From Broker Where (FirstName = @pFirstName Or @pFirstName = 
  '') And (LastName = @pLastName or @pLastName = '')
   And (CompanyName = @pCompanyName or @pCompanyName)
  return 
End

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

terrence_chan99
Starting Member

12 Posts

Posted - 2006-05-31 : 00:34:47

How about if the Broker and BrokerCompany are in different tables?

I tried to do this, but fails.

Thanks you so much for your help.

select
a.BrokerId,
a.BrokerFirmId,
b.Name as FirmName,
a.Title,
a.FirstName,
a.LastName
from
Broker a,
BrokerFirm b
where a.BrokerFirmId = b.BrokerFirmId
and (
(a.Firstname like @Firstname+'%' and @Firstname is not null)
or (a.Lastname like @Lastname+'%' and @Lastname is not null)
or (b.Name like @FirmName+'%' and @Name is not null)
)

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-31 : 01:19:26

It should be somthing liket this

Try this ??


select
a.BrokerId,
a.BrokerFirmId,
b.Name as FirmName,
a.Title,
a.FirstName,
a.LastName
from
Broker a,
BrokerFirm b
where a.BrokerFirmId = b.BrokerFirmId
and (
(a.Firstname like @Firstname+'%' OR @Firstname is not null)
And (a.Lastname like @Lastname+'%' OR @Lastname is not null)
And (b.Name like @FirmName+'%' OR @Name is not null)
)

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-31 : 06:28:50

This article contains everything you've ever wanted to know about this kind of searching (dynamic)...

http://www.sommarskog.se/dyn-search.html

Chiragkhabaria's suggestion is one of the techniques, and is totally fine for your requirements.

By the way, you should lean towards using the ANSI standard for your joins...

select
a.BrokerId,
a.BrokerFirmId,
b.Name as FirmName,
a.Title,
a.FirstName,
a.LastName
from
Broker a
inner join BrokerFirm b on a.BrokerFirmId = b.BrokerFirmId
where (
(a.Firstname like @Firstname+'%' OR @Firstname is not null)
And (a.Lastname like @Lastname+'%' OR @Lastname is not null)
And (b.Name like @FirmName+'%' OR @Name is not null)
)

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-05-31 : 08:58:00

I don't think that you need both parts of this

quote:
(a.Firstname like @Firstname+'%' OR @Firstname is not null)

if the a.FirstName will only return true if @firstName AND the like condition is met.
The @firstName is not null includes that entire set of results.

I think you mean:

(A.firstName like @firstName+'%' or @firstName is null)

but that contradicts null,null,null returning nothing.

Maybe do an


if (coalesce(@firstName,@lastName,@firmName) is null)
Begin
--Return empty set here.
End
Else
Begin
-- Do search here
End

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-31 : 09:21:32

Good spot Corey...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-31 : 10:13:58

opps i think i just copy pasted and replace the and opertor

It should be somthing like this


select
a.BrokerId,
a.BrokerFirmId,
b.Name as FirmName,
a.Title,
a.FirstName,
a.LastName
from
Broker a,
Inner Join 
BrokerFirm b
On a.BrokerFirmId = b.BrokerFirmId
Where 
(a.Firstname like @Firstname+'%' OR @Firstname is null)
And (a.Lastname like @Lastname+'%' OR @Lastname is null)
And (b.Name like @FirmName+'%' OR @Name is null)
)

Thanks Corey

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-31 : 11:03:37

I cut and pasted the same error too

Standing on the shoulders of old ladies...

Okay, ignore me

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-31 : 11:41:19

a.Firstname like @Firstname+'%' OR @Firstname is null

can be replaced by

a.Firstname like ISNULL(@Firstname,'')+'%'

Madhivanan

Failing to plan is Planning to fail

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-05-31 : 12:50:42

quote:
Originally posted by madhivanan

a.Firstname like @Firstname+'%' OR @Firstname is null

can be replaced by

a.Firstname like ISNULL(@Firstname,'')+'%'

Madhivanan

Failing to plan is Planning to fail

Maddy - Is that an efficient substitution?? I would think that the @firstname is null is an easier decision for the query enginge. I don't know for sure though...

Infact, I usually put it first inside a set of ORs:


(@firstName is null or firstName like @firstName + '%')

Corey

Co-worker on children "...when I have children, I'm going to beat them. Not because their bad, but becuase I think it would be fun ..."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-01 : 03:02:40

I prefer to use it to avoid usage of OR. But I think my approach wont return first name which is NULL

Madhivanan

Failing to plan is Planning to fail

cmdr_skywalker
Posting Yak Master

159 Posts

Posted - 2006-06-01 : 03:49:46

Use the charindex since it will return true if the name entered is the second name THERESE for ('MARIA THERESE'), i.e. CHARINDEX(ISNULL(@LastName,''),ISNULL(LastName,''))>0. Note however, that in a huge client table (Millions of records), there can be substantial performance impact (even with LIKE operator). You may resort to creating indexes or index values. See the other threads discussing huge client/person tables.

May the Almighty God bless us all!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources