ALTER proc [dbo].[proc_viewlist] @Table varchar(300), --表名 @Where varchar(7500),--查询条件 @Cou varchar(500),--查询的列,查询全部用* @NewPageIndex int,--当前页码 @PageSize int,--分页条数 @order varchar(100), --排序列, @isDistinct int=0 --是否带distinct 如果带,则isDistinct为1,不带 可以省略此参数 as declare @str varchar(8000) ---------------------------------------------查询总记录数------------------------------------ if @isDistinct <> 0 set @str='select count(1) from (select '+@cou+' from '+@table else set @str='select count(1) from '+@table if len(@where)<>0 set @str=@str+' where '+@where if @isDistinct <> 0 set @str=@str+' )a' ---------------------------------------------查询当前页记录----------------------------------- if @isDistinct <> 0 set @str=@str+' select * from(select *,row_number() over(order by '+@order+' ) as Rownum from ( select '+@cou+' from '+@table else set @str=@str+' select * from ( select '+@cou+' ,row_number() over(order by '+@order+') as Rownum from '+@table if len(@where)<>0 set @str=@str+' where '+@where if @isDistinct <> 0 set @str=@str+' )T1)T ' else set @str=@str+' )T ' set @str=@str+'where Rownum between '+cast((@NewPageIndex-1)*@PageSize+1 as varchar(20))+' and '+cast(@NewPageIndex*@PageSize as varchar(20)) if len(@order)<>0 set @str=@str+' order by '+@order exec(@str) print (@str)
储存过程 CREATE PROCEDURE [dbo].[Zhzuo_GetItemsPage2005] @PageIndex INT, @PageSize INT, @RecordCount INT OUT, @PageCount INT OUT AS /*获取记录数*/ SELECT @RecordCount = COUNT(*) FROM Production.Product /*计算页面数据*/ SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize) /* 基于SQL SERVER 2005 */ SELECT SerialNumber,ProductID,Name FROM (SELECT ProductID,Name,ROW_NUMBER() OVER (ORDER BY ProductID DESC) AS SerialNumber FROM Production.Product ) AS T WHERE T.SerialNumber > (@PageIndex * @PageSize) and T.SerialNumber <= ((@PageIndex+1) * @PageSize)
ALTER proc [dbo].[proc_viewlist]
@Table varchar(300), --表名
@Where varchar(7500),--查询条件
@Cou varchar(500),--查询的列,查询全部用*
@NewPageIndex int,--当前页码
@PageSize int,--分页条数
@order varchar(100), --排序列,
@isDistinct int=0 --是否带distinct 如果带,则isDistinct为1,不带 可以省略此参数
as
declare @str varchar(8000)
---------------------------------------------查询总记录数------------------------------------
if @isDistinct <> 0
set @str='select count(1) from (select '+@cou+' from '+@table
else
set @str='select count(1) from '+@table
if len(@where)<>0
set @str=@str+' where '+@where
if @isDistinct <> 0
set @str=@str+' )a'
---------------------------------------------查询当前页记录-----------------------------------
if @isDistinct <> 0
set @str=@str+' select * from(select *,row_number() over(order by '+@order+' ) as Rownum from ( select '+@cou+' from '+@table
else
set @str=@str+' select * from ( select '+@cou+' ,row_number() over(order by '+@order+') as Rownum from '+@table
if len(@where)<>0
set @str=@str+' where '+@where
if @isDistinct <> 0
set @str=@str+' )T1)T '
else
set @str=@str+' )T '
set @str=@str+'where Rownum between '+cast((@NewPageIndex-1)*@PageSize+1 as varchar(20))+' and '+cast(@NewPageIndex*@PageSize as varchar(20))
if len(@order)<>0
set @str=@str+' order by '+@order
exec(@str)
print (@str)
1、无需给存储过程添加输出参数@PageCount,AspNetPager会自动根据PageSize和RecordCount属性计算总页数;
2、CurrentPageIndex是当前页索引,不是起始的记录索引,记录索引应该是 StartRecordIndex
CREATE PROCEDURE [dbo].[Zhzuo_GetItemsPage2005]
@PageIndex INT,
@PageSize INT,
@RecordCount INT OUT,
@PageCount INT OUT
AS
/*获取记录数*/
SELECT @RecordCount = COUNT(*) FROM Production.Product
/*计算页面数据*/
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
/* 基于SQL SERVER 2005 */
SELECT SerialNumber,ProductID,Name FROM
(SELECT ProductID,Name,ROW_NUMBER() OVER (ORDER BY ProductID DESC) AS SerialNumber FROM Production.Product ) AS T
WHERE T.SerialNumber > (@PageIndex * @PageSize) and T.SerialNumber <= ((@PageIndex+1) * @PageSize)