SQL Paging

This stored procedure provides a simple method of SQL paging

 

CREATE PROCEDURE [dbo].[Up_Guestbook_List]
@PageNumber int,
@PageSize int
as
begin
set nocount on
declare @TotalRows int
declare @FirstRow int
declare @FirstRowId int
declare @PageTotal float
select @TotalRows = count(id) from Guestbook where status = 1
set @PageTotal = ceiling(cast(@TotalRows as float) / cast(@PageSize as float))
if (@PageNumber < 1) set @PageNumber = 1
if (@PageNumber > @PageTotal) set @PageNumber = @PageTotal
select @FirstRow = (@PageNumber - 1) * @PageSize + 1
if (@FirstRow <= @TotalRows)
begin
set rowcount @FirstRow
select @FirstRowId = id
from Guestbook where status = 1
order by 1c
set rowcount @PageSize
select
@PageNumber as PageNumber,
@PageSize as PageSize,
@PageTotal as PageTotal,
*
from Guestbook
where id >= @FirstRowId and status = 1
order by 1
end
set nocount off
end

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: