SQL Server rownumbering and filtered results

Question

I have an application in which I need to display "pages" of data in a list. The basic idea is fairly common. The display shows a list of items and at the bottom of the display are some kind of controls that allow you to go to the next "page" of data.

All well and good. I have this working.

Following is the SQL in a view I am using to support the "next" behavior.

CREATE VIEW CampaignParticipants AS
SELECT  row_number() OVER (ORDER BY TPT.LastName, TPT.FirstName, TPT.DoB) AS RowNumber
        ,CGP.*
        ,TPT.*
FROM    tblCampaignGEDPush CGP
JOIN    tblParticipants TPT
ON      CGP.PartID = TPT.PartID

Here is how I use the VIEW

SELECT  *
FROM    CampaignParticipants
WHERE   RowNumber >= 0
AND     RowNumber <= 100

This simulates grabbing the "first page" of 100 results from the VIEW. Pages thru each set of results just peachy.

Great.. BUT:

As some of you that have dealt with this probably are aware, this is flawed. If I want to search on TPT.LastName like 'R%' and get the first set of results, I'm doomed.

I'm gonna want to start looking at RowNumber = 0, stop at RowNumber = 100, but the "R" results will probably be well outside that range. Upshot: list comes back empty.

And it gets stickier: the user wants to be able to "filter" on LastName, FirstName, DoB, Location, Phone, Zip, anything.

**edit: i can't really put the filter on the "inner" query, as it's in a view, and the filter can change arbitrarily

Anybody have any ideas how to get this result set have a row_number() on the filtered set of results?

Answer 1

Something like this should do...

SELECT * FROM (
SELECT  *, ROW_NUMBER() OVER (ORDER BY LastName, FirstName, DoB) AS __RN
FROM    CampaignParticipants
WHERE   LastName LIKE 'R%') innerData WHERE __RN BETWEEN 1 and 100

However, you should be using the column names, not '*'. I don't know what your tables look like so I can't fill that in for you.

Answer 2

First of all try to use the proc instead of view and as users are asking you to have so much filtering then proc is only solution you got.Inside proc filter the data using all these filters and then generate the row_number and then show say first 100 records or like that.