searching for multiple words - 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)

searching for multiple words

Author

Topic

thiruna
Starting Member

41 Posts

Posted - 2002-07-03 : 06:28:21

Hello friends,

I am using a stored procedure to search multiple fields in a table which is as follows

CREATE PROCEDURE SP_search_user_all
(
@page int,
@recsperpage int,
@querystr varchar(100)
)
AS
-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON

--Create a temporary table
CREATE TABLE #TempItems
(
ID int IDENTITY,
user_no int,
user_name varchar(20),
user_firstname varchar(50),
user_email varchar(50),
user_department varchar(50),
user_active bit
)

-- Insert the rows from tblItems into the temp. table
IF @querystr IS Null

BEGIN
INSERT INTO #TempItems (user_no, user_name, user_firstname, user_email, user_department, user_active)
SELECT user_no, user_name, user_firstname, user_email, user_department, user_active
FROM users

END
ELSE
BEGIN
Select @querystr = '%' + ltrim(rtrim(@querystr)) + '%'
INSERT INTO #TempItems (user_no, user_name, user_firstname, user_email, user_department, user_active)
SELECT user_no, user_name, user_firstname, user_email, user_department, user_active
FROM users
WHERE user_name LIKE @querystr
or user_firstname LIKE @querystr
or user_email LIKE @querystr
or user_department LIKE @querystr
END

-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)

-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT *,
MoreRecords =
(
SELECT COUNT(*)
FROM #TempItems TI
WHERE TI.ID >= @LastRec
) ,
Totalrecords =
(
SELECT COUNT(*)
FROM #TempItems TI
)
FROM #TempItems
WHERE ID > @FirstRec AND ID < @LastRec

-- Turn NOCOUNT back OFF
SET NOCOUNT OFF
GO

But this stored procedure searches only for exact keywords we supply.. I mean if we type a keyword something like "Thiru from India", it searches only for %Thiru from India% but i want it search it as follows.....
It should parse the keywords and search %thiru from India%, %thiru%, %from%, %india%

Please guide me to modify the above stored procedure to search by parsing the keyword....

Many thanks
thiruna@vsnl.com

macka
Posting Yak Master

162 Posts

Posted - 2002-07-03 : 07:03:43

Take a look at this:

[url]http://www.sqlteam.com/item.asp?ItemID=5857[/url]

macka.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources