分页存储过程

CREATE PROCEDURE  page
    @PageIndex INT, /*@PageIndex从计数,0为第一页*/
    @PageSize  INT /*页面大小*/
AS
    declare  @RecordCount INT /*记录数*/
    declare @PageCount INT  /*页数*/
/*获取记录数*/
Select @RecordCount = COUNT(*) FROM USERS
/*计算页面数据*/
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
/*检查页面是否超出记录*/
IF @PageIndex<0
BEGIN
     SET  @PageIndex=0
END
if @PageIndex >@PageCount - 1
BEGIN
     SET  @PageIndex=@PageCount-1
END
/*TOP记录数*/
DECLARE @TOPCOUNT INT
SET @TOPCOUNT = @RecordCount - @PageSize * @PageIndex
DECLARE @SQLSTR NVARCHAR(1000)

IF @PageIndex = 0 OR @PageCount <= 1
BEGIN
    SET @SQLSTR ='Select TOP '+STR(@PageSize)+
     '* FROM USERS'
END
ELSE
BEGIN
    IF @PageIndex = @PageCount - 1
    BEGIN
       SET @SQLSTR ='Select * FROM ( Select TOP  ' + STR(@TOPCOUNT) +
       '* From users  ORDER BY ID DESC) T'
    END
    ELSE
    BEGIN
       SET @SQLSTR =' Select TOP '+STR(@PageSize)+'* FROM (Select TOP ' + STR(@TOPCOUNT) +
       '* FROM users ORDER BY ID  DESC) T ORDER BY ID ASC'
    END
END
ExEC (@SQLSTR)
GO

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/dy19862008/archive/2008/06/05/2515153.aspx