Sqlserver,MySql 通用分页存储过程

SqlServer 分页存储过程

create proc [dbo].[procPage]
@tableName varchar(20),--表名
@showField varchar(100),--要显示的列名
@whereText varchar(500),--where条件(只需要写where后面的语句)
@orderText varchar(500),--排序条件（只需要写order by后面的语句）
@pageSize int,--每一页显示的记录数
@pageIndex int,--当前页
@dataCount int output--总记录数
as
if(len(@whereText)>0)
	set @whereText = ' where '+@whereText
if(len(@orderText)>0)
	set @orderText = ' order by '+@orderText
declare @sql nvarchar(4000)
set @sql = 
'
select * from
(
select row_number() over(order by tempCoulmn) num,* from
(
	select top '+convert(nvarchar(10),@pageIndex*@pageSize)+' '+@showField+',tempCoulmn=0 from '+@tableName+@whereText+@orderText+'
)p1
)p2 where num>'+convert(nvarchar(10),(@pageIndex-1)*@pageSize)
--执行字符串的SQL命令
exec(@sql)
--获取总记录数
set @sql = 'select @dataCount=count(*) from '+@tableName+@whereText
exec sp_executesql @sql,N'@dataCount int output',@dataCount output

MySql 分页存储过程

CREATE PROCEDURE ProcPage(
in tableName varchar(20),#表名
in showField varchar(100),#要显示的列名
in whereText varchar(500),#where条件(只需要写where后面的语句)
in orderText varchar(500),#排序条件（只需要写order by后面的语句）
in pageSize int,#每一页显示的记录数
in pageIndex int,#当前页
out dataCount int#总记录数
)
BEGIN

if (pageSize<1)then
  set pageSize=20;
end if;

if (pageIndex < 1)then
  set pageIndex = 1;
end if;

if(LENGTH(whereText)>0)then
	set whereText=CONCAT(' where 1=1 ',whereText);
end if;

if(LENGTH(orderText)>0)then
	set orderText = CONCAT(' ORDER BY ',orderText);
end if;

set @strsql = CONCAT('select ',showField,' from ',tableName,' ',whereText,' ',orderText,' limit ',pageIndex*pageSize-pageSize,',',pageSize);

prepare stmtsql from @strsql;
execute stmtsql;
deallocate prepare stmtsql;

set @strsqlcount=concat('select count(1) as count into @datacount from ',tableName,'',whereText);
prepare stmtsqlcount from @strsqlcount;
execute stmtsqlcount;
deallocate prepare stmtsqlcount;
set datacount=@datacount;
END;

MySql 循环使用

delimiter $
create procedure test()
begin
	declare i int;
	set i=1;
	while  i<30  do
		select  i;
		set  i=i+1;
	end while ;
end $
call test() $