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