AspNetPager分页存储过程

本文发布于 15 年前,部分内容可能已经失去参考价值。

===========sql2005===========

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_Get_NewsList]--返回新闻列表
(@NodeID        int=null,
@pageIndex      int=null,
@pageSize       int)
AS
BEGIN

with Tmptable as (
 
SELECT ROW_NUMBER() OVER (ORDER BY IfPeak desc, ModifyDate desc) AS RowNo,NewsID,Title,AddDate,IfLink,HtmlNews,HomePic from  T_News  where NodeID=@NodeID )

SELECT * FROM Tmptable where RowNo between (@pageIndex-1)*@pageSize+1 and (@pageIndex-1)*@pageSize+@pageSize

END

===========sql2000===========

ALTER PROCEDURE [dbo].[sp_Get_NewsList]--返回新闻列表
(@NodeID        int=null,
@pageIndex      int=null,
@pageSize       int)
AS

set nocount on

BEGIN
declare @indextable table(id int identity(1,1),nid int)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageIndex-1)*@pageSize
set @PageUpperBound=@PageLowerBound+@pageSize
set rowcount @PageUpperBound

insert into @indextable(nid)
select NewsID from T_News where NodeID=@NodeID ORDER BY IfPeak desc, ModifyDate desc

select NewsID,Title,AddDate,IfLink,HtmlNews,HomePic from T_News O,@indextable t
where O.NewsID=t.nid and t.id
between @PageLowerBound+1 and @PageUpperBound order by t.id

END
set nocount off

转自 网络(如侵权请联系删除) 16 年前
可能相关的内容