CREATE TABLE [TestTable] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[LastName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[Country] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Note] [nvarchar] (2000) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT CountPage ON
declare @i int
set @i=1
while @i<=100000
begin
insert into CountPage([id], FirstName, LastName, Country,Note) values(@i, 'FirstName_XXX','LastName_XXX','Country_XXX','Note_XXX')
set @i=@i+1
end
SET IDENTITY_INSERT CountPage OFF
Create Procedure GetItemPage
@PageIndex int,--当前页,0为第一页
@PageSize int, --每页的大小,每页多少条数据
@TotalCount int output,--总记录数
@PageCount int output--总页数
AS
--获取总记录数
select @TotalCount=count(*) from CountPage
--计算总页数(总记录数/每页记录数)
SET @PageCount = Ceiling(@TotalCount*1.0)/@PageSize
print @PageCount
--TOP记录数()
Declare @TOPCount int
SET @TOPCount = @TotalCount - @PageSize*@PageIndex
DECLARE @SQLSTR NVARCHAR(1000)
IF @PageIndex = 0 OR @PageCount <= 1
Begin
SET @SQLSTR=N'Select TOP '+STR(@PageSize)+ ' * from CountPage ORDER BY ID DESC'--第一页数据
END
ELSE
Begin
IF @PageIndex = @PageCount-1
Begin
SET @SQLSTR=N'Select * from ( Select TOP '+STR(@TOPCount)+ ' ID,FirstName from CountPage ORDER BY ID ASC)
T ORDER BY ID DESC'--最后一页数据
END
ELSE
Begin
SET @SQLSTR =N' SELECT TOP '+STR(@PageSize)+'* FROM (SELECT TOP ' + STR(@TOPCount) +
'ID,FirstName FROM CountPage ORDER BY ID ASC) T ORDER BY ID DESC'
end
END
/*执行*/
EXEC (@SQLSTR)
Create Procedure GetItemPageForSQL2005
@PageIndex int,--当前页数
@PageSize int, --每页大小
@TotalCount int,--总记录数
@PageCount int --总页数
AS
--获取总记录数
Select @TotalCount = Count(*) from CountPage
--获取总页数
Set @PageCount = @TotalCount*1.0/@PageSize
--TOP记录数()
Declare @TOPCount int
SET @TOPCount = @TotalCount - @PageSize*@PageIndex
--第一页
IF @PageIndex = 0 OR @PageCount <=1
Select TOP(@PageSize) * from CountPage ORDER BY ID DESC
ELSE
BEGIN
IF @PageIndex = @PageCount -1
BEGIN
Select * from (Select TOP(@TOPCount) * from CountPage ORDER BY ID ASC) AS T ORDER BY ID DESC
END
ELSE
Select TOP(@PageSize) * from (Select TOP(@TOPCount) * from CountPage ORDER BY ID ASC) AS T ORDER BY ID DESC
END
CREATE PROCEDURE GetItemPageForSQL2005_ROW_NUMBER
@PageIndex INT,
@PageSize INT,
@RecordCount INT OUT,
@PageCount INT OUT
AS
/*获取记录数*/
SELECT @RecordCount = COUNT(*) FROM CountPage
/*计算页面数据*/
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
/* 基于SQL SERVER 2005 */
SELECT * FROM
(SELECT *,ROW_NUMBER() OVER (ORDER BY ID DESC) AS SerialNumber FROM CountPage ) AS T
WHERE T.SerialNumber > (@PageIndex * @PageSize) and T.SerialNumber <= ((@PageIndex+1) * @PageSize)
Exec GetItemPage 0,10,1,1
EXEC GetItemPageForSQL2005 0,10,1,1
EXEC GetItemPageForSQL2005_ROW_NUMBER 0,10,1,1
分页存储过程整理(三种方法)
需积分: 10 56 浏览量
2008-11-11
16:01:22
上传
评论
收藏 1KB RAR 举报
accphuangli
- 粉丝: 0
- 资源: 7