存储过程通过临时表来实现分页的功能

将多个表的查询结果通过新建表来进行分页。过程如下所示：

CREATE PROCEDURE sp_GetShopList
@pagesize int,
@pageindex int,
@strWhere varchar(500)
as
DECLARE @strSQL varchar(5000),@strSQL1 varchar(5000),@strSQL2 varchar(5000),@maxPage int ,@Sqls varchar(500),@RecordCount int ,@SumCount int
IF EXISTS (SELECT * FROM sysobjects WHERE   id =object_id('#temp_shop'))

   DROP TABLE #temp_shop

create table #temp_shop
(
 ID1 int identity(1, 1),
 ID int,
 ShopName varchar(50),
 ChildShop varchar(50),
 Address varchar(200),
 Tel varchar(100),
  IsVip int,
 CityID int
)
 
if @pagesize>0

begin

    SET @strSQL2='select a1.ID, a1.ShopName,a1.ChildShop,a1. Address,a1.Tel, a1.IsVip,a1.CityID from('
    SET @strSQL = ' select distinct  sp_Shop. ID,sp_Shop. ShopName,sp_Shop. ChildShop,sp_Shop. Address,sp_Shop. Tel,sp_Shop. IsVip,sp_Shop.CityID  from sp_Shop '
    SET @strSQL1 = ' select  distinct  sp_Shop. ID,sp_Shop. ShopName,sp_Shop. ChildShop,sp_Shop. Address,sp_Shop. Tel,sp_Shop. IsVip,sp_Shop.CityID  from sp_Shop ,Dev_History'
    
    if @strWhere<>''
     begin
      Select @strSQL=@strSQL2+ @strSQL1+' where '+@strWhere+' and Dev_History.DevType=1 and Dev_History.InfoID=sp_shop.ID union  all '+ @strSQL +' where '+@strWhere +'  and sp_shop.ID not in (select InfoID from Dev_History) ) as a1 '
     end
    else
     begin
      Select @strSQL=@strSQL2+ @strSQL1+' where and Dev_History.DevType=1 and Dev_History.InfoID=sp_shop.ID   union all '+@strSQL +' where sp_shop.ID not in (select InfoID from Dev_History)) as a1'
     end
    Select @strSQL=@strSQL+' order by a1.isvip desc'
    insert into #temp_shop  exec(@strSQL)
  
    --取总数
    Select @SumCount=Count(ID1) from #temp_shop
    if (@SumCount%@pagesize=0)
     BEGIN
      SET @maxPage=@SumCount/@pagesize
     END
    else
    BEGIN
     SET @maxPage=Round(@SumCount/@pagesize,0)+1
    END
    

    Select @Sqls= ' select top '+str(@pagesize)  +'[ID],ShopName,ChildShop,Address,Tel,IsVip,CityID  from #temp_shop
    where ID not in (Select Top (@pagesize*(@pageindex-1))ID FROM #temp_shop order by IsVip'

  
end

 
else
 begin

  SET @strSQL2=' select a1.ID, a1.ShopName,a1.ChildShop,a1. Address,a1.Tel, a1.IsVip,a1.CityID   from('
  SET @strSQL = ' select distinct  sp_Shop. ID,sp_Shop. ShopName,sp_Shop. ChildShop,sp_Shop. Address,sp_Shop. Tel,sp_Shop. IsVip,sp_Shop.CityID  from sp_Shop '
  SET @strSQL1 = ' select  distinct  sp_Shop. ID,sp_Shop. ShopName,sp_Shop. ChildShop,sp_Shop. Address,sp_Shop. Tel,sp_Shop. IsVip,sp_Shop.CityID  from sp_Shop ,Dev_History'

  if @strWhere<>''
   begin
    Select @strSQL= @strSQL2+ @strSQL1+'  where  '+ @strWhere +' and Dev_History.DevType=1 and and  Dev_History.InfoID=sp_shop.ID   union all  '+ @strSQL+  '  where  ' +@strWhere +' and sp_shop.ID not in (select InfoID from Dev_History) ) as a1 order by isvip desc'
   end
  else
  begin
   Select @strSQL= @strSQL2+ @strSQL1+' where  and Dev_History.DevType=1 and  Dev_History.InfoID=sp_shop.ID   union  all '+@strSQL +' where sp_shop.ID not in (select InfoID from Dev_History)) as a1 order by isvip desc  '
  end
  
  insert into #temp_shop  exec(@strSQL)
 end

exec ('select * from #temp_shop')
--print ('select * from #temp_shop')
--Exec( @strSQL)
GO