储存过程实现分页

一：

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