Compound searches - 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

SQL Server Development (2000)

Compound searches

Author

Topic

darenkov
Yak Posting Veteran

90 Posts

Posted - 2005-02-10 : 00:09:19

just wondering how i can use a compound search to allow someone to search under multiple criteria in one hit? is it better to use just one statement or multiple sql statments?

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-10 : 00:46:05

Pass the parameters to a stored procedure - doing it that way means that you can restructure the database if necessary.
accountant type - is that a multiple selection of types? If so pass a csv list. Pass the type IDs rather than the names.
Is phrase a list of keywords?

You will need to split these into individual entries.
for this use http://www.mindsdoor.net/SQLTsql/ParseCSVString.html

now the sp

create proc s_search
@postcode varchar(100) ,
@csvAccountntType varchar(1000) ,
@Keywords varchar(1000)
as

create table @ActIds(id int)
create table @ActTypes(id int)
insert @ActTypes
select *
from dbo.fn_ParseCSVString(@csvAccountntType, ',')
create table @KeywordTbl(s varchar(50))
insert @KeywordTbl
select *
from dbo.fn_ParseCSVString(@Keywords, ' ')

-- get the id's of the accountants - might be more flexible
insert @ActIds
select distinct ac.accountant_id
from Accountant ac
join Accountant_AccountanType acact
on ac.accountant_id = acact.accountant_id
join @ActTypes a
on a.id = acact.AccountanType_id
join Act_Keyword ak
on ac.accountant_id = ak.accountant_id
join Keyword k
on k.Keyword_id = ak.Keyword_id
join @KeywordTbl kt
on kt.s = k.Keyword
where (ac.PostCode = @postcode
or ac.Suburb = @postcode)
go

Then you just need to return the resultset you need.
Do you need to deal with optional parameters? If so set the parameter to null and use that to check.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

darenkov
Yak Posting Veteran

90 Posts

Posted - 2005-02-10 : 01:47:10

i'll try that

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-10 : 02:20:03

How does the ACCOUNTANT_TYPES link to anything?
is ACCOUNTANT_BUSINESS.accountants_id really a misnamed link to ACCOUNTANT_TYPES.type_id

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

darenkov
Yak Posting Veteran

90 Posts

Posted - 2005-02-10 : 05:04:08

nr, sorry i made an error. I have edited my previous post. the type_id can now be found in both tables: ACCOUNTANT_TYPES & ACCOUNTANT_BUSINESS. that is how it should be

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-10 : 14:10:11

Looks like you can only select one type so no need for that csv list.
Send null if all services are selected


create proc s_search
@postcode varchar(100) ,
@Type int ,
@Keywords varchar(1000)
as
	set nocount on

	create table @BusIds(id int)
	create table @KeywordTbl(s varchar(50))
	
	insert @ActTypes
	select *
	from dbo.fn_ParseCSVString(@csvAccountntType, ',')
	
	insert @KeywordTbl
	select *
	from dbo.fn_ParseCSVString(@Keywords, ' ')
	
	-- get the id's of the accountants - might be more flexible
	insert @BusIds
	select distinct b.business_id
	from BUSINESS b
		join ACCOUNTANT_BUSINESS ab
			on ab.business_id = b.business_id
		join BUSINESS_DETAILS bd
			on b.business_id = bd.business_id
		join @KeywordTbl kt
			on ' ' + kt.s + ' ' like '% ' + business_description + ' %'
	where 	(b.PostCode = @postcode or b.Suburb = @postcode)
	and 	(ab.type_id = @AccountType or @Type is null)
	
	select 	*
	from	BUSINESS_DETAILS bd
		join @BusIds bids
			on bd.business_id = bids.id

go

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources