TOP x records - what about next x records?

[waimanlam]

I am trying to implement something a function within SQL server 2000 which allows a user to identify the number of the record returned from a table (it is a bit like record paging). The stored procedure will be using the following syntax:

exec MySPName 'table_name', startRecord, numberofRowsReturn

For example, if startrecord = 1, numberofRowsReturn = 20, the first 20 records in a table will be returned...etc.

I have tried using a combination of 'top x' and 'not exists' but then it does not seem to work.. Anyone has better idea?

Thanks in advance.
Cheers,
Wai-man

[pan]

Try the following stored procedure:


CREATE PROCEDURE MySP @TABLE VARCHAR(30), @Start INT, @NumOfRecords INT AS
SET NOCOUNT ON
DECLARE @STR VARCHAR(3000)
DECLARE @counter INT
SELECT @counter = @Start
SELECT @STR ='DECLARE curs CURSOR SCROLL FOR SELECT * FROM '+@TABLE
EXEC (@STR)
OPEN curs
WHILE @counter < (@Start + @NumOfRecords) AND @@FETCH_STATUS <> -2
BEGIN
FETCH ABSOLUTE @counter FROM curs
SELECT @counter = @counter + 1
END
CLOSE curs
DEALLOCATE curs
GO