根據條件查詢電影並分頁 if exists(select * from sysobjects where name='up_SearchMovie')drop proc up_SearchMoviego use DB_Newsgocreate proc up_SearchMovie@totalCou ...
------------根據條件查詢電影並分頁----------------
if exists(select * from sysobjects where name='up_SearchMovie')
drop proc up_SearchMovie
go use DB_News
go
create proc up_SearchMovie
@totalCount int output,
@totalPage int output,
@size int,
@index int,
@mName nvarchar(50) ='',
@sTime datetime =''
as
---查詢結果 if(@sTime!='')
begin
---計算總記錄數
select @totalCount= count(*) from Movie_Info where MNane like '%'+@mName+'%'and PlayTime =@sTime
select top(@size) m.*,p.province,c.city from Movie_Info m,provinces p,cities c
where
m.PId=p.provinceid
and m.CId=c.cityid
and MId not in (select top((@index-1)*@size) mid from Movie_Info where MNane like '%'+@mName+'%'
and PlayTime =@sTime)
and MNane like '%'+@mName+'%'
and PlayTime =@sTime
order by mid
end
else
begin
---計算總記錄數
select @totalCount= count(*) from Movie_Info where MNane like '%'+@mName+'%' select top(@size) m.*,p.province,c.city from Movie_Info m,provinces p,cities c
where
m.PId=p.provinceid
and m.CId=c.cityid
and mid not in (select top((@index-1)*@size) mid from Movie_Info where MNane like '%'+@mName+'%')
and MNane like '%'+@mName+'%'
order by mid
end
---計算總頁數
set @totalPage=@totalCount/@size if(@totalCount%@size>0)
set @totalPage+=1
