Looping in stored procedures

Hi

On my site I have a search engine which is used by potential customers to find out what courses we have on offer. Everytime something is written into the search engine the search criteria are logged in a table.

All the courses are held in a different table and I have enabled full text search on this table. Only two cloumns are searched for matches this is the course title and keywords.

What I want to be able to do is write a stored procedure which tells me which search criteria did not match any course titles or keywords. So that I can then assess what word people are using to search for courses and then add any which didn't match any courses to the keywords if they are relevant.

What I have done so far is create a view which contains a distinct list of search criteria. All i need to do now is create a stored procedure which loops through the courses table searching for each distinct search criteria.

I know this is possible to do using asp.net but I would like to do this on the database side to speed the process up a bit.

Any help on this matter would be much appreciated as I seem o be goinbg round and round in circles!!!

Thanking you in advance.

SteveSteve:

You have to use a FETCH statement to loop through a result set in SQL Server. However this is VERY inefficient. It is VERY slow. I would not reccomend it to anyone unless it is absolutly necessary.

On the other hand, you should be able to accomplish the search using a query. If you can post some data from your tables and the struc of the tables then I would be glad to help you write a query to get what you need.

HTH|||Hi Wes

Thanks for the reply and offer to help.

I have got two tables:
tblSTATISTCS and tblFACTFILES

tblSTATISTICS is made up of four columns which are ID(Primary Key), Type (different methods of finding a course i.e. search, courselist ect..), Name (what user typed in or selected) and Date

I have now run a view called queSEARCH which picks out all the distinct names which have 'search' as its type. So in effect tblSTATISTIC ahs gone from

ID Type Name Date
1 Search it 11/11/03
2 Courselist Business 11/11/03
3 Search Art 11/11/03
4 Search Web Design 11/11/03

to... queSEARCH

 Name
 it
 Art
Web Design

So I guess what I need to do now is take each row in queSEARCH and use a CONTAINSTABLE statement for tblFACTFILE in a loop?

tblFACTFILE contains quite a few rows so I have shorted this down to the important ones

Course Code CourseTitle Keywords
10001 Art for beginners Artist, painting, drawing
10002 Computing for beginners Web Design, IT, PC
10003 HND in Computing IT, PC, ASP

I hope this makes sence and thanks again for you offer of help.

Steve|||Steve:

I was following you up until the end. What is the end result that you are looking for? I am sure we can do this in one query rather than a loop.|||Wes

What I want is to find all of the word in queSEARCH which do not match anything in either coursetitle or Keywords. So if the word potatoes was added to the list of words people searched with and it was run against the courses I listed earlier, potatoes would remain in the list. Where as everything else (art, it, web design) wouldn't because it matches at least one of the factfiles.

In effect I would like to produce a page of results to show potential keywords that we are not using already within our factfiles.

I hope this is a little clearer.

Cheers
Steve|||You might be better off constructing a SQL string and sp_executing it.|||I think I am now at the point you were at when you started this thread. Due to the result set that you want it can't be done using a sub query (which I am sure you already knew). I now understand you question perfect, in that you must use a Fetch loop to go through the queSearch table one row at a time and then query to tblFactFile to see if there are any matches. You are going to have to use the CONTAINS clause rather than the CONTAINSTABLE because you can use a variable with the CONTAINS where you can't with the CONTAINSTABLE. Here is the code that you were originally looking for:

DECLARE @.currentName as varchar(50)
DECLARE myCursor CURSOR FOR
SELECT [name] FROM queSearch
--your cursor is now filled with the queSearch results
OPEN myCursor
-- Perform the first fetch.
FETCH NEXT FROM myCursor INTO @.currentName
-- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
WHILE @.@.FETCH_STATUS = 0
BEGIN
 Select CourseCode FROM tblFactFile WHERE CONTAINS(Keywords,@.currentName) OR CONTAINS(CourseTitle,@.currentName)
 IF @.@.rowcount = 0
 BEGIN
-- IT did not find any matches, therefore do what you need to do
 END
 -- This is executed as long as the previous fetch succeeds.
 FETCH NEXT FROM myCursor INTO @.currentName
END
CLOSE myCursor
DEALLOCATE myCursor
GO

|||Thanks Wes.

I will give this a try tomorrow and let you know how I get on.|||Wes

That peice of code work great. Thanks very much for taking the time to help.|||I am glad to help.