(转)最牛叉的分页存储过程

 

DECLARE @pagenum AS INT, @pagesize AS INT 
SET @pagenum = 2 
SET @pagesize = 3 
SELECT * 
FROM (SELECT ROW_NUMBER() OVER(ORDER BY newsid DESC) AS rownum,  
        newsid, topic, ntime, hits 
      FROM news) AS D 
WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize 
ORDER BY newsid DESC 


--aspx里?面?只?需?给?SQL传?入?pageid和?条?数?即?可?。?

--CSDN上?还?有?个?存?储?过?程?实?现?分?页?的?代?码?：?

ALTER PROCEDURE news_Showlist 
( 
@tblName   varchar(255),       -- 表?名?
@strGetFields varchar(1000),  -- 需?要?返?回?的?列?
@fldName varchar(255),      -- 排?序?的?字?段?名?
@PageSize   int ,          -- 页?尺?寸?


@PageIndex  int ,           -- 页?码?
@strWhere  varchar(1500),  -- 查?询?条?件?(注?意?: 不?要?加?where) 
@Sort varchar(255)      --排?序?的?方?法?

) 
AS 
declare @strSQL   varchar(5000)       -- 主?语?句?
declare @strTmp   varchar(110)        -- 临?时?变?量?
declare @strOrder varchar(400)        -- 排?序?类?型?

  

if @Sort = ’?desc’?
begin 
set @strTmp = ’?<(select min’?
set @strOrder = ’?order by ’?+ @fldName +’?desc’?
--如?果?@OrderType不?是?，?就?执?行?降?序?，?这?句?很?重?要?！?
end 
else 
begin 
set @strTmp = ’?>(select max’?
set @strOrder = ’?order by ’?+ @fldName +’?asc’?

end 
  
if @PageIndex = 1 
begin 
if @strWhere != ’?’?   
  begin 
set @strSQL = ’?select top ’?+ str(@PageSize) +’?’?+@strGetFields+ ’? from ’?+ @tblName + ’?where ’?+ @strWhere + ’?’?+ @strOrder 
  end 
else 
  begin 
set @strSQL = ’?select top ’?+ str(@PageSize) +’?’?+@strGetFields+ ’? from ’?+ @tblName + ’?’?+ @strOrder 
  end 
--如?果?是?第?一?页?就?执?行?以?上?代?码?，?这?样?会?加?快?执?行?速?度?
end 
else 
begin 
--以?下?代?码?赋?予?了?@strSQL以?真?正?执?行?的?SQL代?码?
set @strSQL = ’?select top ’?+ str(@PageSize) +’?’?+@strGetFields+ ’? from ’?
+ @tblName + ’?where ’?+ @fldName + ’?’?+ @strTmp + ’?(’?+ @fldName + ’?) from (select top ’?+ str((@PageIndex-1)*@PageSize) + ’?’?+ @fldName + ’?from ’?+ @tblName + ’?’?+ @strOrder + ’?) as tblTmp)’?+ @strOrder 

  
if @strWhere != ’?’?
set @strSQL = ’?select top ’?+ str(@PageSize) +’?’?+@strGetFields+ ’? from ’?
+ @tblName + ’?where ’?+ @fldName + ’?’?+ @strTmp + ’?(’?
+ @fldName + ’?) from (select top ’?+ str((@PageIndex-1)*@PageSize) + ’?’?
+ @fldName + ’?from ’?+ @tblName + ’?where ’?+ @strWhere + ’?’?
+ @strOrder + ’?) as tblTmp) and ’?+ @strWhere + ’?’?+ @strOrder 
end 
exec (@strSQL) 
RETURN