paging concept using Sql

I need to make paging navigation in asp.I am using SqlServer2000, I need help on building sql query where I can get only the specified number of records when the next/previous link is clicked.

Here's a good paging example.



<http://www.asp101.com/articles/reco...aging/index.asp>

Here's a stored procedure I've written that can efficiently page through any table, view, sub-query, etc. Simply add this to the database your using and execute with the parameters listed.



All of the grids at this site use this logic...



URL



=============================================



CREATE PROCEDURE Pager



(

@pageSize int,

@whichPage int,

@whichTable nvarchar(50),

@sortField nvarchar(80),

@orderBy nvarchar(4),

@tieBreaker nvarchar(80),

@whereClaus nvarchar(300)

)



AS



declare @vSQL nvarchar(2000)



declare @PAGENO nvarchar(5)

set @PAGENO = convert(nvarchar, @pageSize * (@whichPage + 1))



declare @PAGELEN nvarchar(5)

set @PAGELEN = convert(nvarchar, @pageSize)



declare @revOrderBy nvarchar(4)

if UPPER(@orderBy) = 'ASC'

set @revOrderBy = 'DESC'

else

set @revOrderBy = 'ASC'



declare @whereToUse nvarchar(300)

if @whereClaus = ''

set @whereToUse = ''

else

set @whereToUse = ' WHERE ' + @whereClaus + ' '



set @vSQL = 'SELECT COUNT(*) AS RowsCount FROM [' + @whichTable + ']' + @whereToUse



EXECUTE sp_executesql @vSQL

/* EXECUTE (@vSQL) */



set @vSQL = 'SELECT * FROM (SELECT TOP ' + @PAGELEN + ' * FROM '

set @vSQL = @vSQL + '(SELECT TOP ' + @PAGENO + ' * FROM [' + @whichTable + '] '

set @vSQL = @vSQL + @whereToUse

set @vSQL = @vSQL + 'ORDER BY [' + @sortField + '] ' + @orderBy + ', [' + @tieBreaker + '] ASC) AS t1 '

set @vSQL = @vSQL + 'ORDER BY [' + @sortField + '] ' + @revOrderBy + ', [' + @tieBreaker + '] DESC) AS t2 '

set @vSQL = @vSQL + 'ORDER BY [' + @sortField + '] ' + @orderBy + ', [' + @tieBreaker + '] ASC'



EXECUTE sp_executesql @vSQL

/* EXECUTE (@vSQL) */



RETURN