分页存储过程

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