用存储过程实现数据分页
参考:http://blog.csdn.net/wellknow/archive/2004/07/29/55167.aspx
用的是NOT IN 的方法
![](/Images/OutliningIndicators/None.gif)
/**************************
* 创建人 :Even
* 创建时间 :2005-12-13
* 最近修改时间 :
* 功能描述 :
*
* 用存储过程实现数据分页
*
* @Table : 表名, 可以是一张表,也可以是一个SQL语句
* @PageSize : 页码大小
* @CurrentPageIndex : 当前页面, CurrentPageIndex=1 表示第一页
* @KeyField : 关键字段域
* @Sort : 排序字段
* ************************/
![](/Images/OutliningIndicators/None.gif)
--------------------------------------------------
-- DataPager
--------------------------------------------------
![](/Images/OutliningIndicators/None.gif)
ALTER PROCEDURE dbo.DataPager
(
@Table nvarchar(2048),
@PageSize int,
@CurrentPageIndex int,
@KeyField VARCHAR(255),
@Sort VARCHAR(255)
![](/Images/OutliningIndicators/None.gif)
)
AS
![](/Images/OutliningIndicators/None.gif)
declare @SqlStr as nvarchar(4000)
![](/Images/OutliningIndicators/None.gif)
set @SqlStr = 'select top ' + cast(@CurrentPageIndex * @PageSize as varchar(20)) + ' * from ' + @Table + ' t'
set @SqlStr = @SqlStr + ' where t.' + @KeyField + ' NOT IN'
set @SqlStr = @SqlStr + ' (select TOP ' + cast((@CurrentPageIndex-1)*@PageSize as varchar(20)) + ' ' + @KeyField + ' from '
set @SqlStr = @SqlStr + ' ' + @Table + ' ' + @Sort + ') ' + @Sort
![](/Images/OutliningIndicators/None.gif)
--print @SqlStr
![](/Images/OutliningIndicators/None.gif)
execute sp_executeSql @SqlStr
用的是NOT IN 的方法
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)