Kevin,

>I like your solution...I've posted mine on my blog (www.TheBakersDozen.net)
>
>I admit, I like the LIMIT statement - it doesn't do "everything" that ROW_NUMBER OVER (PARTITION BY...) can do - but I admit, it is nice.
>
>If nothing else, people can see there are different ways to approach things.

Just to the audience:

It is important to know what this discussion is about, so lets set this straight:

- This is NOT a VFP vs .NET discussion, so please don't place any comments dragging in this argument. The discussion is language transparents.
- This is about SQL-Server 2005 features vs databases that do not use those features
- This is about whether using a SP or using Dynamic SQL (SPT).


My observations,

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().

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.

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 (We have gone through that a while ago, when trying to solve the problem of identifying the one invoice that exceeds a certain threshold).

Then the choice for SP versus SPT
As expected I did choose for SPT, you for SP to pass the SQL from the database to the application layer.

On the whole, your SP solution is far more complex than the generation of one SQL command that has everything in it. Granted, I gave you a solution where the WHERE clause was passed to the routine and you baked everything in the SP, but it also has risks in beeing less optimizable because of all the CASE WHEN's in there (The optimizer cannot predict the index to use and forces a table or index scan).

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'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 know there might be other arguments (like security) reasons for doing SP, but in this case it is not the most efficient and simple implementation, if it only were for the reason that you now have to maintain the logic on Two (!) tiers if the requirement changes and the client wants a extra selection criteria. In the SPT choice, you only have to maintain the application layer as both the HTML and SQL generation are maintained there.

Walter,