c# - Problem with SQL Query Tracking -

okay here's issue.

the user can go onto site , retrieve 8 records @ time, he/she given option load more. these 8 records can sorted param passed proc. when these 8 records on front end, have id's (hidden user though obviously), id's not in specific order because records sorted variety of possible things.

when click "load more", should able next 8 records database, sorted in same fashion first 8 were.

for example, "give me top 8 records sorted age". -> click load more -> give me next 8 oldest records without showing me onces saw.

how can call proc , make sure none first result set returned though? want return 8 records @ time efficiency reasons.

select top 8       m.message,       m.votes,       (geography::point(@latitude, @longitude, 4326).stdistance(m.point)) * 0.000621371192237334 distance,       m.location,       datediff(hour,m.timestamp, getdate()) age,       m.messageid,       ml.voted,       ml.flagged           tblmessages m     left join tblipmessagelink ml on m.messageid = ml.messageid           m.timestamp >= dateadd(day, datediff(day, 0, @date), 0)       ,       m.timestamp < dateadd(day, datediff(day, 0, @date), 1)       order         case when @sort = 'votes1' m.votes end desc,         case when @sort = 'votes2' m.votes end asc,         case when @sort = 'age1' datediff(hour,m.timestamp, getdate()) end asc,         case when @sort = 'age2' datediff(hour,m.timestamp, getdate()) end desc,         case when @sort = 'distance1' (geography::point(@latitude, @longitude, 4326).stdistance(m.point)) * 0.000621371192237334 end asc,         case when @sort = 'distance2' (geography::point(@latitude, @longitude, 4326).stdistance(m.point)) * 0.000621371192237334 end desc       end 

that's current query. how change work paging?

use row_number

example

call 1

;with cte as(select *,row_number() over( order name) rows sysobjects)  select * cte rows between 1 , 8 order rows 

call 2

;with cte as(select *,row_number() over( order name) rows sysobjects)  select * cte rows between 9 , 16 order rows 

of course want use parameters instead of hardcoding numbers, way can reuse query, if column can sorted arbitrarily might need use dynamic sql

edit, here should like, want return max rownumber know how many rows can potentially returned can make rows per page dynamic, in case like

where rows between @startrow , (@startrow + @rowsperpage) -1

make sure read dynamic search conditions in t-sql version sql 2008 see how can optimize plan reuse , better plan in general

anyway, here proc, untested of course since can't run here

declare @startrow int,@endrow int --select @startrow =1, @endrow = 8   ;with cte (select row_number() on (order         case when @sort = 'votes1' m.votes end desc,         case when @sort = 'votes2' m.votes end asc,         case when @sort = 'age1' datediff(hour,m.timestamp, getdate()) end asc,         case when @sort = 'age2' datediff(hour,m.timestamp, getdate()) end desc,         case when @sort = 'distance1' (geography::point(@latitude, @longitude, 4326).stdistance(m.point)) * 0.000621371192237334 end asc,         case when @sort = 'distance2' (geography::point(@latitude, @longitude, 4326).stdistance(m.point)) * 0.000621371192237334 end desc       end) rows        m.message,       m.votes,       (geography::point(@latitude, @longitude, 4326).stdistance(m.point)) * 0.000621371192237334 distance,       m.location,       datediff(hour,m.timestamp, getdate()) age,       m.messageid,       ml.voted,       ml.flagged           tblmessages m     left join tblipmessagelink ml on m.messageid = ml.messageid           m.timestamp >= dateadd(day, datediff(day, 0, @date), 0)       ,       m.timestamp < dateadd(day, datediff(day, 0, @date), 1)       )        select *       cte rows between @startrow , @endrow     order rows