多条件分页：
—–分页存储过程
–判断存储过程是否存在
if OBJECT_ID(‘proc_Page‘) is not null
–删除存储过程
drop proc proc_Page
go
–创建存储过程
create proc proc_Page
–参数
@ClaId int=0, –班级Id
@Sage varchar(20)=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 * from v_student‘;
–条件
set @sql+=@sqlWhere;
set @sql+=‘ ) as temp
where rid>‘+STR(@rid);
exec (@sql);