At 03:00 AM 24/11/2007, you wrote:

>From: "Helen Borrie" <helebor@...>
>>
>8<SNIP>8
>
>Pardon my jumping in.
>
>Maybe I'm wrong (seems more often nowadays), but I thought the OP just
>wanted to store the count for each search item and increment it each time
>that search item is used(searched for). If it's the first time the search
>item is used, then insert a new record and set the count to 1. Either way,
>the LastSearch field is updated with the current date/time.

That's what I thought after the second lot of code he posted, too. But then he subsequently added that he wanted to update the existing records in the same table if records were found. That introduces 2 problems:

1. unless there is one and exactly one record containing the search string, i.e., the searched field is unique in the table, he would get a "multiple rows in singleton select" exception, not a result.

2. he needs to get back the id and lastsearch values for all of the found records.

That introduces the need for the FOR loop and the safeguards against it becoming infinite.

>I'm not sure why there is an ID field because it isn't inserted if a new
>record is needed in any of the OPs posts. It might just be a generator-fed
>field using an insert trigger but it isn't clear from any of the posts what
>it is or contains.
>
>1. User initiates a search of [something, don't know what or where] using a
>search string.
>
>2. The search code calls the procedure with the search string to update the
>table.
>
>3. The procedure checks to see if the string has been searched for before.
>
>4. If it has, it increments the count and returns that number and also
>updates the LastSearch field with the current timestamp.
>
>5. If it hasn't, it inserts a new record with that search string and returns
>a count of 1.
>
>I don't think he wanted to store each time a value was searched in a
>separate record with a timestamp. Like I said, though, I've been wrong a lot
>lately so maybe I should hone up on my reading skills. :)

He wants to update existing records as well. So the one-record approach can only work if the stored search key is unique. But he claimed an IF EXISTS() predicate wouldn't cut what he wanted to do, which suggested that he's storing multiple records keyed on id and searchstring.

But I'm wrong often too, especially at 2:30 a.m. It wouldn't be the first time I've kicked myself for wasting and hour and a half trying to understand and fix someone's SP instead of heading for bed. ;-)

>One thing is certain, from all the differing answers, it's clear that the
>original problem wasn't stated as clearly as it should be.

I notice the OP has decided to blag Firebird and return to MSSQL server (which I don't mind *at all* ;-) ). It's nice that he thinks we're such a fab list but I think he's got a bit to learn about what constitutes a good problem description. The first block of sample code looked like something simple. It gathered moss as it rolled along...like that radio game "Twenty Questions".

If the OP is still around, he might like to read this, in case he ever goes to use a support list again:

http://www.catb.org/~esr/faqs/smart-questions.html

^ heLen