mysql 存储过程多条件分页_sql多条件分页存储过程

-----分页存储过程

--判断存储过程是否存在

if OBJECT_ID('proc_Page') is not null

--删除存储过程

drop proc proc_Page

go

--创建存储过程

create  proc proc_Page

--参数

@ClaId int=0,               --班级Id

@Sage datetime=null,        --出生日期

@Sname nvarchar(10)=null,   --学生名称

@SmallScore int=0,          --小成绩

@BigScore int=0,            --大成绩

@CID int=0,                 --课程Id

@Tname nvarchar(10)=null,   --讲师名字

@PageIndex int,    --当前页

@PageSize int,    --每页条数

@TotalCount int out,  --总条数

@order int=1                --1升序  0降序

as

--变量

declare

@sql varchar(max),      --总sql

@sqlWhere varchar(max), --条件

@rid int,              --分页条件

@countSql nvarchar(max)  --个数sql

set @sql='';

set @sqlWhere=' where 1=1';

set @rid=(@PageIndex-1)*@PageSize;

set @countSql='select @total=count(1)

from Classs

join Student on Classs.ClaID=Student.ClaID

join SC      on sc.SID=Student.SID

join Course  on Course.CID=sc.CID

join Teacher on Teacher.TID=Course.TID';

--条件班级Id

if @ClaId!=0

begin

set @sqlWhere+=' and Classs.ClaId='+str(@ClaId);

end

----出生日期

if @Sage is not null

begin

set @sqlWhere+=' and Sage='+@Sage;

end

--学生名称

if @Sname is not null

begin

set @sqlWhere+=' and Sname like ''%'+@Sname+'%''';

end

--小成绩

if @SmallScore !=0

begin

set @sqlWhere+=' and Score>='+STR(@SmallScore);

end

--大成绩

if @BigScore !=0

begin

set @sqlWhere+=' and Score<='+STR(@BigScore);

end

--课程Id

if @CID!=0

begin

set @sqlWhere+=' and CID='+str(@CID);

end

--讲师名字

if @Tname is not null

begin

set @sqlWhere+=' and Tname like ''%'+@Tname+'%''';

end

--总条数

set @countSql+=@sqlWhere;

--执行计算条数的sql语句并且给输出参数赋值

exec sp_executesql  @countSql, N'@total int out',@total=@TotalCount out

--排序

declare @o varchar(4);

if @order=1

begin

set @o='asc';

end

if @order=0

begin

set @o='desc';

end

--总sql

set @sql='select top '+str(@PageSize)+' * from

(

select Classs.ClaID,Classs.ClassName,Classs.Counts,Student.Sage,Student.SID,Student.Sname,Student.Ssex,

SC.scid,SC.score,Course.CID,Course.Cname,Teacher.TID,Teacher.Tname,ROW_NUMBER() over(order by score '+@o+') as rid

from Classs

join Student on Classs.ClaID=Student.ClaID

join SC      on sc.SID=Student.SID

join Course  on Course.CID=sc.CID

join Teacher on Teacher.TID=Course.TID';

--条件

set @sql+=@sqlWhere;

set @sql+=' ) as temp

where rid>'+STR(@rid);

exec (@sql);