分页 查询 显示的 存储过程

------------根据条件查询电影并分页----------------
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