用存储过程分页吧~~~~ select*from(select row_number() over(order by id) as row,*from dbo.Person where isDel=0) as tt where row between (@pageIndex-1)*@pageSize+1 and @pageIndex*@pageSize
存储过程 CREATE PROCEDURE dbo.CreateSimple ( @PageIndex int, @PageSize int) AS BEGIN --定义三个变量: -- @PageLowerBound :所取出记录的下限. -- @PageUpperBound: 所要取出记录的上限. -- @TotalRecords: 返回记录总数,主要用于页面的计算. DECLARE @PageLowerBound int DECLARE @PageUpperBound int DECLARE @TotalRecords int --计算上下限的值. SET @PageLowerBound=@PageIndex * @PageSize SET @PageUpperBound=@PageLowerBound+@PageSize-1 --创建临时表: --IndexId是标识,自动增长1; --SimpleId由数据表[Simple]填充; CREATE TABLE #PageIndexForSimple ( IndexId int identity(1,1) NOT NULL, SimpleId int ) --填充临时表 INSERT INTO #PageIndexForSimple(SimpleId) SELECT s.[SimpleId] FROM [Simple] s --取得记录总数,其实影响行数就是记录总数 SELECT @TotalRecords=@@ROWCOUNT SELECT s.* FROM [Simple] s,#PageIndexForSimple p WHERE s.[SimpleId]=p.[SimpleId] AND p.[IndexId]>=@PageLowerBound AND P.[IndexId]<=@PageUpperBound ORDER BY s.[Simple] --返回记录总数. RETURE @TotalRecords END
CREATE PROCEDURE dbo.CreateSimple
( @PageIndex int,
@PageSize int)
AS
BEGIN
--定义三个变量:
-- @PageLowerBound :所取出记录的下限.
-- @PageUpperBound: 所要取出记录的上限.
-- @TotalRecords: 返回记录总数,主要用于页面的计算.
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @TotalRecords int
--计算上下限的值.
SET @PageLowerBound=@PageIndex * @PageSize
SET @PageUpperBound=@PageLowerBound+@PageSize-1
--创建临时表:
--IndexId是标识,自动增长1;
--SimpleId由数据表[Simple]填充;
CREATE TABLE #PageIndexForSimple
(
IndexId int identity(1,1) NOT NULL,
SimpleId int
)
--填充临时表
INSERT INTO #PageIndexForSimple(SimpleId)
SELECT s.[SimpleId]
FROM [Simple] s
--取得记录总数,其实影响行数就是记录总数
SELECT @TotalRecords=@@ROWCOUNT
SELECT s.*
FROM [Simple] s,#PageIndexForSimple p
WHERE s.[SimpleId]=p.[SimpleId]
AND p.[IndexId]>=@PageLowerBound
AND P.[IndexId]<=@PageUpperBound
ORDER BY s.[Simple]
--返回记录总数.
RETURE @TotalRecords
END