>Walter,
>
>- This is NOT a VFP vs .NET discussion, so please don't place any comments dragging in this argument. The discussion is language transparents.
>
>I know you meant that for the audience - however, it should be noted that you used VFP for part of the solution. :)
>
>I don't mean that in a VFP vs .NET thing (as I've never mentioned .NET in this discussion, and have no intentions of doing so).

Too late! ;)

>
>Looking at your solution I cannot draw the conclusion that the CTE's, RANK() and ROW_NUMBER() is making the task you've outlined at the start of the thread significantly easier than the other approaches. It seems to me that MySQL is more than capable to implement this, even much easier (my observation), than SQL server 2005. Personally I found the solution I gave earlier for SQL2000 (using multiple TOPs) at least as acceptable as the one using ROW_NUMBER().
>
>To show the result number, ROW_NUMBER() works fine - you already acknowledged that you had to utilize a 2nd capability (using LIMIT and then incrementing a variable to return the row number). Now, I could be difficult here and say that I'm using one function and you're using two, but in all honesty, I'd be splitting hairs. IMO, both are equally fine for this.
>
>FWIW, where ROW_NUMBER really shines is when you need to assign sequential numbers by groups.
>
> CTEs (IMO) are creating the possibility to write SQL statements that normally will be written in two statments using a temp tables. Also, solving hierarchical problems are easier to do with CTEs. The first technically does not add much value to get certain problems solved, the second has more value into it as this *IS* a new feature that is able to solve hierarchical problems more easily.
>
>Actually, CTE's have a multiplier effect, as you can nest CTEs. But the true power of CTEs is in recursive queries.
>
>I've looked at the RANK() and ROW_NUMBER() functions before to solve certain problems, but it did not cut it, as it is still a poor mechanism to solve certain RECORD ORIENTED problems
>
>Can you be more specific? As it stands, I don't agree with this statement.
>
>As I mentioned earlier, ROW_NUMBER is great for assigning values based on groups. Suppose you want to assign a ranking number to sales within product and within customer - you can do that in the PARTITION clause of the ROW_NUMBER statement. I think you might want to take a second look at the ranking functions (ROW_NUMBER is but one of them).
>
>
>On the whole, your SP solution is far more complex than the generation of one SQL command that has everything in it.
>
>I don't see how you can arrive at that conclusion. The approaches are very different. I don't see either one as "more complex". My two cents is that you're penning it as more complex because it's a paradigm shift from the way you normally approach things. :)
>
>Those who regularly write SPs might argue that yours is more complex. I really wouldn't agree with that either. They are just two very different ways to solve things.
>
>Having said that....
>
>- I can test the stored procedure independently of the application.
>- If there's a bug or something that needs to be tweaked, I don't have to redeploy an application component. (True, if there's a new search requirement, a new component needs to be rolled out)
>- The stored proc is essentially the API for the task at hand.
>- While you provided a solution that fits all the criteria, you have introduced two tools: VFP and the database. I'm only using the database.
>
>
>I'd also would favour
>Lastname LIKE @AlphaChar
>over
>SUBSTRING(LastName,1,1) >= RTRIM(@AlphaChar)
>and make sure that the @Alphachar has a '%' padded to it.
>
>I like that better, you're right, thanks.
>
>I'd also encourage people not to go into joins if not neccesary, esspecially in table with a lot of records. In your case when you've got only a few possible selections of statuses (lets say below a hundred) it might be faster to use an
>Status IN(1, 6, 7, 9, etc)
>solution. It would avoid the optimized to do uneccesary joins and enabled existing compound indexes to retrieve the information faster. I've been able to optimize queries running from minutes to subsecond responsetimes.
>
>I tested this out before, when I was using the XML approach. There is really no difference in performance. The table variable is in memory 99.99% of the time. There's an additional argument to be made that variable selections are more commonly passed these days as XML than as a CSV, but that's an argument for another day. :)
>
>
>I know there might be other arguments (like security) reasons for doing SP
>
>Yes, and that is an important factor. Like it or not, some installations ONLY use stored procedures as the means for data access.
>
> but in this case it is not the most efficient and simple implementation
>
>That's your opinion, but IMO you haven't offered anything to substantiate that. So I don't concur with it (nor am I saying the reverse). One could make the reverse argument that the SP itself is more self-documenting.
>
>But regardless, thanks for doing this - at least people can see different ways to tackle this issue.