How to select only row 5 to 19 or row 20 to 299 from a table?

This can done be very easily with a temporary table or (in SQL Server 2000) a variable of type table.

Also, you may find Experts more responsive if you took the time to re-read the EE Guidelines regarding grading standards.  For the record:
 
Questions Asked 6
Last 10 Grades Given C D D B B  
Question Grading Record 5 Answers Graded / 5 Answers Received

Anthony

you can use cursor.
i will test it and give you a result

I would actually recommend against a cursor or temporary table for this application. While I understand the motivation, I think a one-time hit might work better performance-wise than the constant traffic that would be necessary to browse the list. Have a question about browsing potentially "hundreds of thousands of rows" in a ListView, but that's another topic. An alternative is to populate the ListView using a disconnected recordset. This recordset can be created asynchronously at the start of the application to minimize the impact to users.

Here's an example of the kind of thing I'd suggest:

CREATE TABLE stp_YourProcedure
     @sintStartRecord INTEGER,
     @sintEndRecord INTEGER
AS
BEGIN
     CREATE TABLE #ptblRecords
     (
     intRecordNumber INTEGER IDENTITY(1,1),
     strFieldOne VARCHAR(100)
     )

     INSERT #ptblRecords
     SELECT FieldOne
     FROM YourTable
     
     SELECT * FROM #ptblRecords
     WHERE intRecordNumber BETWEEN @sintStartRecord AND @sintEndRecord

     DROP TABLE #ptblRecords
END

--This will return records 15-25 assuming that you have an identity field.

SELECT TOP 10 * FROM TABLENAME WHERE IDENTITY_FIELD_FALUE >= 15

If you have some kind of key value or sequence (for example, name or id number), then you do something like the following:

--this would actually likely be application code,
--not db code, but the logic is the same

DECLARE @lastKey INT
DECLARE @numRows INT
SET @lastKey = 0  --if first time thru

SET @numRows = 20  --or whatever

Loop:
--this code would be passed as a batch to db to exec
SET ROWCOUNT @numRows
SELECT ...
FROM ...
WHERE keyCol > @lastKey
--put returned rows into list

--user uses current list, requests next page ...
--save value of last entry added to list and get more
SET @lastKey = last_key_in_list
GOTO Loop

You can use subquery like this:
--
SELECT TOP [n] k_field, field_a, field_b, ....
FROM myTable
WHERE k_field not in (
  select TOP [m] k_field
  FROM myTable
  ORDER BY k_field)
ORDER BY k_field
--

where:
[n] is the number of rows to be returned (row per page)
[m] is the ordinal position of the first row returned (0=return the first row, 1=return the 2 row, etc.).

gnicoli,

The only problem with this approach is that [n] and [m] have to be constants.  This means that in a Stored Procedure the SQL Statement would have to be written as dynamic SQL.

Anthony

That is true.  Set ROWCOUNT (unlike TOP) will allow a variable.

Anthony