储存过程实现分页

一：

Create PROCEDURE [dbo].[Basic_Pagination2000]

@tblName varchar(255), -- 表名

@fidlelist varchar(2000), --要查询字段

@fldName varchar(255), -- 排序字段

@PageSize int, -- 页尺寸

@PageIndex int, -- 页码

@IsReCount bit, -- 返回记录总数, 非0 值则返回

@OrderType bit, -- 设置排序类型, 非0 值则降序

@strWhere varchar(1000) -- 查询条件(注意: 不要加where)

AS

declare @strSQL varchar(6000) -- 主语句

declare @strTmp varchar(100),@tmpwhere varchar(200) -- 临时变量

declare @strOrder varchar(400) -- 排序类型

if @OrderType != 0

begin

set @strTmp = '<(select min'

set @strOrder = ' order by [' + @fldName +'] desc'

end

else

begin

set @strTmp = '>(select max'

set @strOrder = ' order by [' + @fldName +'] asc'

end

set @tmpwhere='';

if(@strWhere!='')

begin

set @tmpwhere=' where ';

end

if @PageIndex = 1

begin

set @strSQL = 'select top '

+ str(@PageSize) +' '+@fidlelist+' '+'from ['

+ @tblName + '] ' + @tmpwhere + ' ' + @strOrder

end

else

begin

set @strSQL = 'select top '

+ str(@PageSize) + ' '+@fidlelist+' '+'from ['

+ @tblName + '] where

[' + @fldName + ']' + @strTmp + '(['

+ @fldName + ']) from

(select top ' + str((@PageIndex-1)*@PageSize) + ' ['

+ @fldName + '] from

[' + @tblName + '] ' + @tmpwhere + ' '

+ @strOrder + ') as tblTmp)

' + @tmpwhere + ' ' + @strOrder

end

exec(@strSQL)

if @IsReCount != 0

begin

set @strSQL = 'select count(*)

as Total from [' + @tblName + ']'+ @strWhere

exec (@strSQL)

end

二：

ALTER PROCEDURE [dbo].[Basic_Pagination2005]

@tblName nvarchar(200), --表名

@fidlelist nvarchar(1000), --要查询字段

@fldName nvarchar(100), --排序字段

@PageSize int, --页尺寸

@PageIndex int, --页码

@IsReCount bit , -- 返回记录总数, 非0 值则返回

@OrderType bit, -- 设置排序类型, 非0 值则降序

@strWhere nvarchar(1000) --查询条件

AS

declare @sqlstr nvarchar(4000),

@tmpwhere nvarchar(4000),@tmporder nvarchar(100)

BEGIN

if @OrderType != 0

begin

set @tmporder = @fldName +' desc '

end

else

begin

set @tmporder = @fldName +' asc '

end

set @tmpwhere='';

if(@strWhere!='')

begin

set @tmpwhere=' where '+@strWhere;

end

set @sqlstr=N'select * from

(select '+@fidlelist+', ROW_NUMBER() OVER(order

by '+@tmporder+') as row from '+@tblName+@tmpwhere+')

tmp where row between '+cast

(((@PageIndex-1)*@PageSize+1) as nvarchar)+' and '+cast

(@PageIndex*@PageSize as nvarchar);

exec sp_executesql @sqlstr

if @IsReCount != 0

begin

set @sqlstr=N'select count(*) as Total from '+ @tblName+@tmpwhere

exec sp_executesql @sqlstr

end

END