searching the database

[crystalized]

I have set up search pages to search my database. My database contains 3 basic things firms, employees and projects. Each of these 3 things is represented by a series of tables. To implement a search I have split the 3 things into 3 search pages.

Here is my stored procedure for the firm retrieval (I know it is probably just terrible but I am really pretty new to stored procedures)

Alter Procedure stpFirmSearchForm
/*
This procedure is designed to do a keyword search on firm tables
for the member firms search form general keyword search.

Simply returns all firm Id's satisfying the search criteria.
*/
	(
		@vKeywords varchar(70)
	)
As
	Select logFirmID from tblLogin
	Where logFirmID in(
			(Select distinct frID as firm from tblFirm
			 Where	frName like '%'+@vKeywords+'%' or
					frProv like '%'+@vKeywords+'%' or
					frCity like '%'+@vKeywords+'%' or
					frCountry like '%'+@vKeywords+'%')
			Union
			(Select distinct fdeFirmID as firm from tblFirmDescription
			 Where fdeDescription like '%'+@vKeywords+'%')
			Union
			(Select distinct fprFirmID as firm from tblFirmPredecessor
			 Where fprPredecessorName like '%'+@vKeywords+'%')
			Union
			(Select distinct fawFirmID as firm from tblFirmAward
			 Where fawAward like '%'+@vKeywords+'%')
			Union
			(Select distinct frdcFirmID as firm from tblFirmDiscipline
			 where frdcDiscID in(Select dcID from tblDisciplineLU
								 Where dcDescription like '%'+@vKeywords+'%'))
						)
		and logCurrent=1
	Order by logServiceLevel ASC
	
	return

The outer part with the tblLogin (it contains some info along with the firmID) is designed to filter out non current members and to order the rest by the service level they have paid for.

So 3 questions:
1. Can anyone offer me suggestions for why or why not to keep this stored procedure as it is, and suggestions for changes that would make it better.
2. How do I handle the case where a user has entered more than one word to search on?
3. If I have projects to retrieve where the identifiers consist of the firmID and a projectID composite key. I know how to retrieve the identifiers with a series of selects and unions but then I am just stumped as to how I would filter out non current members and order them by the service level.

Any help or good references anyone can offer me would be very greatly 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]