mysql sqlserver分页_SqlServer、MySql万能分页代码

sql数据库中常用的分页 我做了一个万能的 用的上的小伙伴拿去耍吧

go  ----SqlServer万能分页代码

create procedure [dbo].[sp_datapager]

@pagesize int,--每一页的大小

@pageindex int,--页码数

@tablename varchar(Max),--表的名称

@keycolumn varchar(20),---主键id

@columns varchar(200),--要查询出列的名称

@where varchar(200),---查询条件

@orderby varchar(100),---排序方式

@recordcount int out--输出参数，非0则返回要查询表的总记录数

as

declare @sql nvarchar(3000)

declare @rcsql nvarchar(1000)

set @rcsql='select @rc=count(*) from '+@tablename

set @sql='select top '+convert(varchar(3),@pagesize)+' '+ @columns+' from '+

@tablename +' where '+@keycolumn+' not in(select top '+

convert(varchar(10),(@pageindex-1)*@pagesize)+' '+@keycolumn+

' from '+@tablename+')'

if (@where!='')

begin

set @rcsql='select @rc=count(*) from '+@tablename+' where '+@where

set @sql='select top '+convert(varchar(3),@pagesize)+ @columns+' from '+

@tablename +' where '+@keycolumn+' not in(select top '+

convert(varchar(10),(@pageindex-1)*@pagesize)+' '+@keycolumn+

' from '+@tablename+' where '+@where+') and '+@where

end

if (@orderby!='')

begin

if (@where!='')

begin

set @sql='select top '+convert(varchar(3),@pagesize)+ @columns+' from '+

@tablename +' where '+@keycolumn+' not in(select top '+

convert(varchar(10),(@pageindex-1)*@pagesize)+' '+@keycolumn+

' from '+@tablename+' where '+@where+' order by '+@orderby+') and '+

@where+' order by ' +@orderby

end

else

begin

set @sql='select top '+convert(varchar(3),@pagesize)+ @columns+' from '+

@tablename +' where '+@keycolumn+' not in(select top '+

convert(varchar(10),(@pageindex-1)*@pagesize)+' '+@keycolumn+

' from '+@tablename+' order by '+@orderby+')'+' order by ' +@orderby

end

end

declare @param nvarchar(100)

set @param='@rc int output'

exec sp_executesql @sql

exec sp_executesql @rcsql,@param,@rc=@recordcount output

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

MySql通用分页加条件

delimiter //

drop procedure if exists `WebPager`;

create DEFINER=`root`@`%` procedure WebPager

(

in tableName varchar(4000),-- 表关系

in selectField varchar(4000),-- 要查询的字段

in strwhere varchar(4000),-- 条件

in ordering varchar(1000),-- 排序字段

in sort int,-- 排序方式 0表示顺序，1表示倒序

in pageIndex int,-- 当前页数

in pageSize int ,-- 每页显示的记录数

out rows int -- 总行数

)

begin

-- declare sqlstr varchar(4000);

set @sqlstr=concat('select SQL_CALC_FOUND_ROWS ',selectField,' from ',tableName,' where 1=1 ');

if strwhere is not null then -- 判断条件是否为空

set @sqlstr=concat(@sqlstr,' and ',strwhere);

end if;

if ordering is not null then

if sort=0 then

set @sqlstr=concat(@sqlstr,' order by ',ordering);

end if;

if sort=1 then

set @sqlstr=concat(@sqlstr,' order by ',ordering,' desc');

end if;

end if;

set @sqlstr=concat(@sqlstr,' limit ',(pageIndex-1)*pageSize,',',pageSize);

-- select sqlstr;

PREPARE distSQL FROM @sqlstr;

EXECUTE distSQL;

DEALLOCATE PREPARE distSQL;

set rows=FOUND_ROWS();-- 获取总记录数

end; //

-- call WebPager('card_manager','*','Del_Flag=0 and Balance>1000','Card_Code',1,1,2,@rows); select @rows;