数据库分页存储过程

表名：可以为多表联合查询后作为一个新的表名来使用

表名也可以为视图名（不推荐）

表名可以是多个表，连接条件写在where中，用where代替on

MySQL

CREATE PROCEDURE Common_GetPagedList
(
p_cloumns varchar(500),
p_tables varchar(100),
p_where varchar(4000),
p_order varchar(100),
p_pageindex int,
p_pagesize int,
out p_recordcount int,
out p_pagecount int

)
begin
declare v_sqlcounts varchar(4000);
declare v_sqlselect varchar(4000);

if(p_cloumns is null or p_cloumns='') then set p_cloumns = ' * '; end if;
if(p_where is null or p_where='') then set p_where = ' 1=1 '; end if;
if(p_order is null or p_order='') then set p_order = ' Id desc '; end if;

#拼接查询总记录的SQL语句
set v_sqlcounts = concat('select count(1) into @recordcount from ',p_tables,' where ',p_where);
#select v_sqlcounts;leave $$;
set @sqlcounts = v_sqlcounts;
prepare stmt from @sqlcounts;
execute stmt;
deallocate prepare stmt;
#获取动态SQL语句返回值
set p_recordcount = @recordcount;
#根据总记录跳数计算出总页数
set p_pagecount = ceiling((p_recordcount+0.0)/p_pagesize);
if p_pageindex <1 then
set p_pageindex = 1;
elseif p_pageindex > p_pagecount and p_pagecount <> 0 then
set p_pageindex = p_pagecount;
end if;
#拼接分页查询记录的动态SQL语句
set v_sqlselect = concat('select ',p_cloumns,' from ',p_tables,' where ',p_where,' order by ',p_order,' limit ',(p_pageindex-1)*p_pagesize,' , ',p_pagesize);
select v_sqlselect;
set @sqlselect = v_sqlselect;
prepare stmtselect from @sqlselect;
execute stmtselect;
deallocate prepare stmtselect;
end

 SQL分页

USE [CRM]
GO

/****** Object:  StoredProcedure [dbo].[Common_GetPagedList]    Script Date: 07/17/2016 21:54:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[Common_GetPagedList]
(
@TableName nvarchar(100), --表名
@ColumnNames nvarchar(1000) = '*', --字段名集合(全部字段为*，其它逗号分隔)
@OrderClause nvarchar(1000) =N' Id desc ', --排序从句(不包含order by)
@WhereClause nvarchar(1000) =N' 1=1 ',    --条件从句(不包含where)
@PageSize int = 0, --每页记录数(0为所有)
@PageIndex int = 1,     --页索引(从1开始)
@TotalRecord int output,     --返回总记录数
@PageCount   int output     --返回总页数
)
AS
 
BEGIN 
if (@ColumnNames is null or @ColumnNames='') set @ColumnNames=' * '
if (@WhereClause is null or @WhereClause='') set @WhereClause=' 1=1 '
if (@OrderClause is null or @OrderClause='') set @OrderClause=' Id desc '
 
--处理开始点和结束点
Declare @StartRecord int;
Declare @EndRecord int; 
Declare @TotalCountSql nvarchar(1200); 
Declare @SqlString nvarchar(4000);    

--统计记录
if(@TotalRecord is null OR @TotalRecord>=0)
begin
SET @TotalCountSql= N'select @TotalRecord = count(*) from ' + @TableName +' where '+@WhereClause; 
--select @TotalCountSql
EXEC sp_executesql @TotalCountSql,N'@TotalRecord int out',@TotalRecord output;--返回总记录数
SET @PageCount = ceiling( @TotalRecord*1.0/@PageSize)
end
 
if @PageSize>0
begin    
if @PageIndex<1 set @PageIndex=1
set @StartRecord = (@PageIndex-1)*@PageSize + 1    
set @EndRecord = @StartRecord + @PageSize - 1 
set @SqlString = N'select row_number() over (order by '+ @OrderClause +') as rowId,'+@ColumnNames+' from '+ @TableName+' where '+@WhereClause;
set @SqlString ='select * from (' + @SqlString + ') as t where rowId between ' + ltrim(str(@StartRecord)) + ' and ' + ltrim(str(@EndRecord));
end
else 
begin
set @SqlString='select '+@ColumnNames+' from '+ @TableName+' where '+@WhereClause +' order by '+@OrderClause
end
--select @SqlString
Exec(@SqlString)
END

GO

 SQL2

USE [DBCommonManage]
GO
/****** Object:  StoredProcedure [dbo].[spPagination]    Script Date: 10/24/2016 11:53:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spPagination]
     @TableName varchar(255), -- 表名
     @InnerJoin varchar(500),  --inner john连接的表
   --  @InnerJoinCondition varchar(255),  --inner john连接条件 
     @SelectFields varchar(1000) = '*', -- 需要返回的列
     @OrderName varchar(255)='', -- 排序的字段名
     @PageSize int = 10, -- 页尺寸
     @PageIndex int = 1, -- 页码
     @Count bit = 0, -- 返回记录总数, 非0 值则返回
     @OrderType bit = 0, -- 设置排序类型, 非0 值则降序
     @WhereCondition varchar(1500) = '' -- 查询条件(注意: 不要加where) 
AS 

declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型

if @Count != 0 
     begin 
         if @WhereCondition !='' 
             set @strSQL = 'select count(*) as Total from [' + @TableName + '] '+@InnerJoin +' where '+@WhereCondition 
         else 
             set @strSQL = 'select count(*) as Total from [' + @TableName + '] '+@InnerJoin +' ' 
     end 
     --以上代码的意思是如果@Count传递过来的不是0，就执行总数统计。以下的所有代码都是@Count为0的情况

else 
     begin 
         if @OrderType != 0 
             begin 
                 set @strTmp = '<(select min' 
                 set @strOrder = ' order by [' + @OrderName +'] desc' 
                 --如果@OrderType不是0，就执行降序，这句很重要！
             end 
        
         else 
             begin 
                 set @strTmp = '>(select max' 
                 set @strOrder = ' order by [' + @OrderName +'] asc' 
             end 
             if @PageIndex = 1 
                 begin 
                     if @WhereCondition != '' 
                         set @strSQL = 'select top ' + str(@PageSize) +' '+@SelectFields+ ' from [' + @TableName + '] '+@InnerJoin +' where ' + @WhereCondition + ' ' + @strOrder 
                     else 
                         set @strSQL = 'select top ' + str(@PageSize) +' '+@SelectFields+ ' from ['+ @TableName + '] '+@InnerJoin + @strOrder 
                         --如果是第一页就执行以上代码，这样会加快执行速度
                 end 
             else 
                 begin 
                     --以下代码赋予了@strSQL以真正执行的SQL代码
                     set @strSQL = 'select top ' + str(@PageSize) +' '+@SelectFields+ ' from ' 
                         + @TableName + ' '+@InnerJoin+ ' where [' + @OrderName + ']' + @strTmp + '(['+ @OrderName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['+ @OrderName + '] from [' + @TableName + '] '+@InnerJoin +' ' + @strOrder + ') as tblTmp)'+ @strOrder 
                         if @WhereCondition != '' 
                     set @strSQL = 'select top ' + str(@PageSize) +' '+@SelectFields+ ' from ' 
                         + @TableName + ' '+@InnerJoin+ ' where [' + @OrderName + ']' + @strTmp + '([' 
                         + @OrderName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' 
                         + @OrderName + '] from [' + @TableName + '] '+@InnerJoin +'  where ' + @WhereCondition + ' ' 
                         + @strOrder + ') as tblTmp) and ' + @WhereCondition + ' ' + @strOrder
     end 
end 

print(@PageIndex)

exec (@strSQL)