set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[pagination]
@TableName varchar(255), -- 表名
@PrimaryKey varchar(255),--主键或者唯一约束字段
@DoCount int = 2, -- 需不需要进行总数统计.2是统计条数及查询 1是只统计总条数,不进行查询 0进行分页查询,不进行统计
@StrWhere varchar(1500) = '', -- 查询条件 (注意: 不要加 where)
@OrderField varchar(255)=@PrimaryKey, -- 排序的字段名,默认按照主键排序
@PageIndex int = 1, -- 页码
@PageSize int = 10, -- 页尺寸
@OrderType bit = 0 ,-- 设置排序类型, 非 0 值则降序
@StrGetFields varchar(1000) = '*'-- 需要返回的列
AS
declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
/********************************************
@DoCount传递过来的不是0,就执行总数统计
********************************************/
if @DoCount = 1
begin
if @StrWhere!=''
set @strSQL = 'select count(*) as Total from ' + @TableName + ' where '+@strWhere
else
set @strSQL = 'select count(*) as Total from ' + @TableName
execute(@strSQL)
end
/********************************************
以下的所有代码都是@DoCount为0的情况:
********************************************/
if @DoCount=0
begin
/********************************************
确定是升序还是降序
********************************************/
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = 'order by ' + @OrderField +' desc'
end
else
begin
set @strTmp = '>(select max'
set @strOrder = 'order by ' + @OrderField +' asc'
end
/**********************************************
为了加快执行速度,判断一下是不是第一页
***********************************************/
if @PageIndex = 1
begin
if @strWhere != ''
set @strSQL ='select top ' + str(@PageSize) +' '+@StrGetFields+ ' from ' + @TableName + ' where ' + @strWhere + ' ' + @strOrder
else
set @strSQL ='select top ' + str(@PageSize) +' '+@strGetFields+ ' from '+ @TableName + ' '+ @strOrder
end
/**********************************************
不是第一页
***********************************************/
else
begin
if @strWhere=''
set @strSQL='select top '+str(@PageSize)+' '+@strGetFields+' from '+ @TableName +' where '+@PrimaryKey+@strTmp+'('+@PrimaryKey+') as '+@PrimaryKey+' from (select top '+str((@PageIndex-1)*@PageSize)+' '+@PrimaryKey+' from '+@TableName+' '+@strOrder+') as T) '+@strOrder
else
set @strSQL='select top '+str(@PageSize)+' '+@strGetFields+' from '+ @TableName +' where '+@PrimaryKey+@strTmp+'('+@PrimaryKey+') as '+@PrimaryKey+' from (select top '+str((@PageIndex-1)*@PageSize)+' '+@PrimaryKey+' from '+@TableName+' where '+@StrWhere+' '+@strOrder+') as T) and '+@StrWhere+' '+@strOrder
end
--print @strSQL --打印sql语句
execute(@strSQL)
end
if @DoCount=2
begin
--记录数
if @StrWhere!=''
set @strSQL = 'select count(*) as Total from ' + @TableName + ' where '+@strWhere
else
set @strSQL = 'select count(*) as Total from ' + @TableName
execute(@strSQL)
--查询
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = 'order by ' + @OrderField +' desc'
end
else
begin
set @strTmp = '>(select max'
set @strOrder = 'order by ' + @OrderField +' asc'
end
/**********************************************
为了加快执行速度,判断一下是不是第一页
***********************************************/
if @PageIndex = 1
begin
if @strWhere != ''
set @strSQL ='select top ' + str(@PageSize) +' '+@StrGetFields+ ' from ' + @TableName + ' where ' + @strWhere + ' ' + @strOrder
else
set @strSQL ='select top ' + str(@PageSize) +' '+@strGetFields+ ' from '+ @TableName + ' '+ @strOrder
end
/**********************************************
不是第一页
***********************************************/
else
begin
if @strWhere=''
set @strSQL='select top '+str(@PageSize)+' '+@strGetFields+' from '+ @TableName +' where '+@PrimaryKey+@strTmp+'('+@PrimaryKey+') as '+@PrimaryKey+' from (select top '+str((@PageIndex-1)*@PageSize)+' '+@PrimaryKey+' from '+@TableName+' '+@strOrder+') as T) '+@strOrder
else
set @strSQL='select top '+str(@PageSize)+' '+@strGetFields+' from '+ @TableName +' where '+@PrimaryKey+@strTmp+'('+@PrimaryKey+') as '+@PrimaryKey+' from (select top '+str((@PageIndex-1)*@PageSize)+' '+@PrimaryKey+' from '+@TableName+' where '+@StrWhere+' '+@strOrder+') as T) and '+@StrWhere+' '+@strOrder
end
--print @strSQL --打印sql语句
execute(@strSQL)
end