多表查询分页存储过程

调用：Exec Sp_AllSiteSearch 10,1,'关键字','',''

存储过程：
Create Proc Sp_AllSiteSearch
@pagesize int,
@pageindex int,
@key varchar(100),
@dt datetime,
@itype varchar(10)
As
/*
Declare @pagesize int,@pageindex int,@key varchar(100),@dt datetime,@itype varchar(10)
Select @pagesize =10,@pageindex =10,@key ='我',@itype=''
*/
Set NoCount On
Declare @pagecount int,@count int,@news_count int,@house_count int,@esf_count int,@zf_count int,@sql nvarchar(4000),@top int,@str1 varchar(200),@str2 varchar(200),@str3 varchar(200),@showcount int,@gtv_count int,@str4 varchar(200)
Select @news_count=0,@house_count=0,@esf_count=0,@zf_count=0,@sql='',@count=0,@str1='',@str2='',@str3='',@pagecount=1,@showcount=0,@gtv_count=0
create table #t(id int identity(1,1),img varchar(300),title varchar(200),content varchar(1000),author varchar(100),
 hits varchar(15),dt varchar(10),itype varchar(10),cid varchar(10),classid varchar(10),folder varchar(10))
If(@key<>'')
Begin
 If(IsDate(@dt)=1)
 Begin
  Select @str1=' And Convert(varchar(10),adddate,120)>='''+Convert(varchar(10),@dt,120)+'''',@str2=' And Convert(varchar(10),PubDate,120)>='''+Convert(varchar(10),@dt,120)+'''',
   @str3=' And Convert(varchar(10),h_datetime,120)>='''+Convert(varchar(10),@dt,120)+'''',@str4=' And Convert(varchar(10),mydate,120)>='''+Convert(varchar(10),@dt,120)+''''
 End
 /*新闻*/
 Set @sql='Select @news_count=count(*) From news..t_news Where deltf=0 And (title like ''%'+@key+'%'' Or author like ''%'+@key+'%'') '+@str1
 Exec sp_executesql @sql,N'@news_count int OUTPUT,@key varchar(100),@dt datetime',@news_count OUTPUT,@key,@dt
 /*新房*/
 Set @sql='Select @house_count=count(*) From main..t_main Where isRecyle=0 And HouseName like ''%'+@key+'%'' '+@str2
 Exec sp_executesql @sql,N'@house_count int OUTPUT,@key varchar(100),@dt datetime',@house_count OUTPUT,@key,@dt
 Set @sql='Select @house_count=@house_count+count(*) From main..t_Article Where deltf=0 And (title like ''%'+@key+'%'' Or author like ''%'+@key+'%'') '+@str1
 Exec sp_executesql @sql,N'@house_count int OUTPUT,@key varchar(100),@dt datetime',@house_count OUTPUT,@key,@dt
 /*二手房*/
 Set @sql='Select @esf_count=count(*) From esf..t_house Where h_infostate=0 And h_title like ''%'+@key+'%'' And h_infotype=''出售'' '+@str3
 Exec sp_executesql @sql,N'@esf_count int OUTPUT,@key varchar(100),@dt datetime',@esf_count OUTPUT,@key,@dt
 /*租房*/
 Set @sql='Select @zf_count=count(*) From esf..t_house Where h_infostate=0 And h_title like ''%'+@key+'%'' And h_infotype=''出租'' '+@str3
 Exec sp_executesql @sql,N'@zf_count int OUTPUT,@key varchar(100),@dt datetime',@zf_count OUTPUT,@key,@dt
 /*视频*/
 Set @sql='Select @gtv_count=count(*) From video..t_video Where title like ''%'+@key+'%'' '+@str4
 Exec sp_executesql @sql,N'@gtv_count int OUTPUT,@key varchar(100),@dt datetime',@gtv_count OUTPUT,@key,@dt

 /*计算翻页*/
 Set @showcount=@news_count+@house_count+@esf_count+@zf_count+@gtv_count
 Set @count=@showcount
 /*新闻*/
 If(@itype='1')
 Begin
  Set @count=@news_count
 End
 /*新房*/
 If(@itype='2')
 Begin
  Set @count=@house_count
 End
 /*二手房*/
 If(@itype='3')
 Begin
  Set @count=@esf_count
 End
 /*租房*/
 If(@itype='4')
 Begin
  Set @count=@zf_count
 End
 /*视频*/
 If(@itype='5')
 Begin
  Set @count=@gtv_count
 End
 Set @pagecount=@count/@pagesize
 if(@count>0)
 Begin
  if(@count%@pagesize>0)
  Begin
   Set @pagecount=@pagecount+1
  End
  if(@pageindex<1)
  Begin
   Set @pageindex=1
  End
  if(@pageindex>@pagecount)
  Begin
   Set @pageindex=@pagecount
  End
  Set @top=@pagesize*@pageindex
  
  /*新闻*/
  If(@itype='1' Or @itype='')
  Begin
   Set @sql='Insert Into #t(img,title,content,author,hits,dt,itype,cid,classid,folder) Select Top '+Cast(@top as varchar)+' photourl,title,Cast(intro as varchar(1000)),author,hits,Convert(varchar(10),adddate,120),''1'',a.id cid,c.classid,c.folder
    From news..t_news a left join news..t_class c On a.tid=c.id Where a.deltf=0 And (title like ''%'+@key+'%'' Or author like ''%'+@key+'%'') '+@str1+' Order By adddate Desc '
   Exec(@sql)
  End
  /*新房*/
  If(@itype='2' Or @itype='')
  Begin
   Set @sql='Insert Into #t(img,title,content,author,hits,dt,itype,cid,classid,folder) Select Top '+Cast(@top as varchar)+' logo,HouseName,Position,'''',click,Convert(varchar(10),PubDate,120),''2'',id,'''',''''
    From main..t_main Where HouseName like ''%'+@key+'%'' And isRecyle=0 '+@str2+' order by PubDate DESC '
   Exec(@sql)
   Set @sql='Insert Into #t(img,title,content,author,hits,dt,itype,cid,classid,folder) Select Top '+Cast(@top as varchar)+' photourl,title,Cast(intro as varchar(1000)),author,hits,Convert(varchar(10),adddate,120),''2'',a.id cid,c.classid,c.folder
    From main..t_Article a left join main..t_class c On a.tid=c.id Where a.deltf=0 And (title like ''%'+@key+'%'' Or author like ''%'+@key+'%'') '+@str1+' Order By adddate Desc '
   Exec(@sql)
  End
  /*二手房*/
  If(@itype='3' Or @itype='')
  Begin
   Set @sql='Insert Into #t(img,title,content,author,hits,dt,itype,cid,classid,folder) Select Top '+Cast(@top as varchar)+' h_img,h_title,h_remark,'''',h_clickcount,Convert(varchar(10),h_datetime,120),''3'',h_id,'''',''''
    From esf..t_house Where h_infostate=0 And h_title like ''%'+@key+'%'' And h_infotype=''出售'' '+@str3+' Order By h_datetime Desc '
   Exec(@sql)
  End
  /*租房*/
  If(@itype='4' Or @itype='')
  Begin
   Set @sql='Insert Into #t(img,title,content,author,hits,dt,itype,cid,classid,folder) Select Top '+Cast(@top as varchar)+' h_img,h_title,h_remark,'''',h_clickcount,Convert(varchar(10),h_datetime,120),''4'',h_id,'''',''''
    From esf..t_house Where h_infostate=0 And h_title like ''%'+@key+'%'' And h_infotype=''出租'' '+@str3+' Order By h_datetime Desc '
   Exec(@sql)
  End
  /*视频*/
  If(@itype='5' Or @itype='')
  Begin
   Set @sql='Insert Into #t(img,title,content,author,hits,dt,itype,cid,classid,folder) Select Top '+Cast(@top as varchar)+' Case When IsNull(Cast(img2 As varchar(300)),'''')='''' Then Cast(img As varchar(300)) Else Cast(img2 As varchar(300)) End,title,content,'''',hit,mydate,''5'',id cid,'''',''''
    From video..t_video Where title like ''%'+@key+'%'' '+@str4+' Order By mydate Desc '
   Exec(@sql)
  End
 -- select * from #t order by dt desc
  Set @sql='Delete #t Where id not in (Select Top '+Cast(@top as varchar)+' id From #t Order By dt Desc)'
  Exec(@sql)
  Declare @topnum int
  Set @topnum=(@pageindex-1)*@pagesize
  Set @sql='Delete #t Where id in (Select Top '+Cast(@topnum as varchar)+' id From #t Order By dt Desc)'
  Exec(@sql)
 End
End
Select '全部' name,'' itype,@showcount num
Union All
Select '新闻' name,'1',@news_count
Union All
Select '新房' name,'2',@house_count
Union All
Select '二手房' name,'3',@esf_count
Union All
Select '租房' name,'4',@zf_count
Union All
Select '视频' name,'5',@gtv_count

Select @pagecount pagecount,@count

Select * From #t Order By dt Desc
Drop Table #t
Set NoCount Off
Go