SqlServer 預存程序分頁

標籤：style   blog   color   io   ar   div   sql   size   

一：預存程序實現分頁  例子：查詢樓盤列表 ALTER procedure [dbo].[BM_GetBlockList]@pageIndex int,-------頁碼數@PageSize int,---------展示的條數@cityId intasdeclare @sql nvarchar(max) ,        @sql2 nvarchar(max)        set @sql=‘  from AgentRecmd_Block ab             LEFT JOIN BASE_BLOCK block on ab.BlockID=block.ROW_ID            LEFT JOIN BASE_AREA AREA ON BLOCK.AREA_ID=AREA.ROW_ID            LEFT JOIN BASE_DISTRICT DIS ON AREA.REGION_ID=DIS.ROW_ID            LEFT JOIN BASE_CITY CITY ON DIS.CITY_ID=CITY.ROW_ID            where block.Gcflag=0 and block.IS_APPROVE=0  and ab.GCFLAG=0            and CITY.ROW_ID=‘+convert(nvarchar(200),@cityId)+‘ and block.IsAPP=1 ‘                 set @sql2=‘select top ‘+ convert(nvarchar(200),@PageSize)+‘ block.ROW_ID, block.BLOCK_NAME,DIS.DISTRICT_NAME,block.AVG_PRICE_WEB,block.HOT_VIEW_WEB,block.HOUSING_PROPERY,ab.EndTime ‘+  @sql +‘ and ab.ROW_ID not in (select top ‘+convert(nvarchar(200),(@pageIndex-1)*@PageSize)+‘ ab.ROW_ID ‘[email protected]+‘ order by ab.EndTime )‘ EXEC(@sql2);