still searching

[crystalized]

I posted a message earlier with an awful stored procedure in it. I am sure everyone was just being polite by not telling me how bad it was. Well I have a slightly different scenario now.

To complete a search my users can enter a keyword, and make 2 selections to refine their search.

The first selection is for the location. Now the location selected can restrict the number of possible firms that match the criteria. The second selection will likewise restrict the number of possible matches. Then finally there is the keyword that I want to look in several tables for matches.

My question is how do I refine my search using the criteria I mentioned while keeping my stored procedure relatively fast.

I am considering a pattern something like this in my stored procedure

(Select fdeFirmID from tblFirm,tblDescription
where fdeFirmID=frID and
      frProv=@cProvIn and
      fdeDescription like '%'+@vKeywordIn+'%')
Union
(....Next select here...)

I know there are probably cursor method that would allow me to find the firms that match the location and then for each of them find the firms that suit the other criteria and finally for the filtered firms find the ones that match the keyword. But I have heard that cursors are usually quite slow which is one of the reasons I am avoiding them (the other being my lack of knowledge of how to use them).

If anyone can tell me if cursors or multiple select statements (with possible nested selects) are more efficient it would at least give me a starting point.
[sig]<p>Crystal<br><a href=mailto:crystals@genesis.sk.ca>crystals@genesis.sk.ca</a><br><a href= > </a><br>--------------------------------------------------<br>
Experience is one thing you can't get for nothing.<br>
-Oscar Wilde<br>
[/sig]

 

[crystalized]

Other question,

Since the user may choose to use all the search options or none I think I have to tailor each of the searches based on which options they have selected. Since there are 3 options and each has a potential to be &quot;on&quot; or &quot;off&quot; that gives me a total of 8 situations I need to cover.

Should I have If statements covering the 8 situations in one stored procedure or should I have 8 different stored procedures called as the users selections demand?

Any response would be highly appreciated. [sig]<p>Crystal<br><a href=mailto:crystals@genesis.sk.ca>crystals@genesis.sk.ca</a><br><a href= > </a><br>--------------------------------------------------<br>
Experience is one thing you can't get for nothing.<br>
-Oscar Wilde<br>
[/sig]

 

[MalcolmW]

A simple example of this (I think) would be a fictional database that contains names, astrological sign, hair color, and eye color. The values for hair color are blond, black, red, brown, NULL (some of us are folically challenged), etc. There are similar values for eye color.
So, you want to pass hair color, eye color, astrological sign, and come up with you a list of matches. And you want to have some way of passing a value for &quot;ALL&quot;.
As you can see, a parameter value of &quot;ALL&quot; will prevent any records from being excluded - which is what you want an &quot;ALL&quot; to do.
Despite the OR statements in the SELECT statement, this type of query is optimized efficiently by SQL Server, probably because once the parameters have values, the OR clauses simplify.

CREATE PROCEDURE spExample
@HairColor varchar(15),
@EyeColor varchar(15),
@Astrologic varchar(15)
AS
SELECT *
FROM People
WHERE (
@HairColor = 'ALL' OR
@HairColor = People.HairColor
) AND
(
@EyeColor = 'ALL' OR
@EyeColor = People.EyeColor
) AND
(
@Astrologic = 'ALL' OR
@Astrologic = People.Astrologic
)
[sig][/sig]

 

[crystalized]

I think I have come to the conclusion that perhaps separating my 8 possible options into 8 seperate stored procedures is really going to be the easiest way for me, given my lack of experience in the creation of stored procedures. Yesterday I had some success I think in coming up with the stored procedure for the case where all the parameters are present. Although the idea that Malcolm presents may affect the eventual implementation I use.

This is what I arrived at. Again perhaps it is not perfect but I understand it which is very important.

Alter Procedure stpFirmAllSearch
/*
This procedure searches for firms when all parameters are entered on
the search form
*/
	(
		@vKeyword varchar(70),
		@cProv char(2),
		@nDiscID int
	)
As
	Select distinct frID 
	from	tblFirm Left Outer Join tblFirmDescription on frID=fdeFirmID
			 Left Outer Join tblFirmPredecessor on frID=fprFirmID
			 Left Outer Join tblFirmAward on frID=fawID
			 Left Outer Join tblFirmDiscipline on frID=frdcFirmID
	where	Upper(frProv)=@cProv and
			frdcDiscID=@nDiscID and
				(frName like '%'+@vKeyword+'%' or
				frCity like '%'+@vKeyword+'%' or
				frCountry like '%'+@vKeyword+'%' or
				fdeDescription like '%'+@vKeyword+'%' or
				fprPredecessorName like '%'+@vKeyword+'%' or
				fawAward like '%'+@vKeyword+'%')			 
	return

[sig]<p>Crystal<br><a href=mailto:crystals@genesis.sk.ca>crystals@genesis.sk.ca</a><br><a href= > </a><br>--------------------------------------------------<br>
Experience is one thing you can't get for nothing.<br>
-Oscar Wilde<br>
[/sig]

 

[TomSark]

Try using the EXECUTE statement and build your query dynamically based on the criterita.


Tom [sig][/sig]