sqlserver分页显示

USE [sq_donglong] GO /****** 对象: StoredProcedure [sq_donglong].[Achi_News_List] 脚本日期: 12/05/2009 11:03:56 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [sq_donglong].[Achi_News_List] @iPageCount int=2 OUTPUT,--总页数 @CountsIndex int =2 OUTPUT,--总记录数 @iPageSize int=2,--每页条数 @iPageIndex int =2,--当前页 @StartDate nvarchar(100) ='',--开始日期 @EndDate nvarchar(100) ='',--结束日期 @SearchKeyword nvarchar(500)='',--产品名称 @SortID nvarchar(10)='',--类别ID @AreaID nvarchar(10)='',--地区ID @ProductProperty nvarchar(10)='',--信息性质 @IsOk nvarchar(10)=''--是否审核 AS --创建临时表 create table #t([ProID] [int] IDENTITY(1,1),[ID] [int] NULL,[NewsNameSi] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL,[SortID] [int] NULL,[SortPath] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL,[GroupID] [nvarchar] (18) COLLATE Chinese_PRC_CI_AS NULL,[Exclusive] [nvarchar] (2) COLLATE Chinese_PRC_CI_AS NULL,[SourceSi] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL,[SmallPic] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL,[BigPic] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL,[ContentSi] [ntext] NULL,[ManNum] [int] NULL CONSTRAINT [DF_#t_Num1] DEFAULT (0),[EndDate] [datetime] NULL,[ClickNumber] [int] NULL,[AddTime] [datetime] NULL,[UploadFiles] [ntext] COLLATE Chinese_PRC_CI_AS,[NewFlag] [bit] NULL,[CommendFlag] [bit] NULL,[IsOk] [bit] NULL,[xuhao] [int] NULL,[Force] [bit] NULL,[AreaID] [int] NULL) --设置IDENTITY_INSERT为on向表中的标识列插入显示值 --set IDENTITY_INSERT #t ON ---变量allNums declare @allNums as decimal --查询条件 declare @datawhere as nvarchar(4000) set @datawhere='' select @allNums=count(ID) from sq_donglong.Achi_News --向临时表中写入数据 declare @strsql nvarchar(4000) --创建查询语句 if(@SearchKeyword<>'') set @datawhere=@datawhere+' and NewsNameSi like ''%'+@SearchKeyword+'%'' ' if(ISDATE(@StartDate)=1) set @datawhere=@datawhere+' and AddTime>='''+(@StartDate)+'''' if(ISDATE(@EndDate)=1) set @datawhere=@datawhere+' and AddTime<='''+(@EndDate)+'''' if(IsNumeric(@SortID)=1) set @datawhere=@datawhere+' and SortID='+@SortID+' ' if(IsNumeric(@AreaID )=1) set @datawhere=@datawhere+' and AreaID ='+@AreaID +' ' if(IsNumeric(@ProductProperty )=1) set @datawhere=@datawhere+' and ProductProperty ='+@ProductProperty+' ' if(IsNumeric(@IsOk)=1) set @datawhere=@datawhere+' and IsOk ='+@IsOk+' ' set @strsql='select * from (select top '+str(@allNums)+' ID,NewsNameSi,ContentSi,ManNum,EndDate,SmallPic,SortID,SortPath,ClickNumber,AddTime,IsOk,xuhao,Force,AreaID from sq_donglong.Achi_News where Force=1 and isDate(EndDate2)=1 and EndDate2>GetDate() '+@datawhere+' order by xuhao asc)a union all select * from (select top '+str(@allNums)+' ID,AreaID from sq_donglong.Achi_News where isnumeric(ID)=1 '+@datawhere+' and ID not in (select top '+str(@allNums)+' ID from sq_donglong.Achi_News where Force=1 and isDate(EndDate2)=1 and EndDate2>GetDate() '+@datawhere+') order by AddTime desc)b ' --print @strsql --return declare @strsql2 nvarchar(4000) set @strsql2=@strsql --插入 set @strsql='insert into #t(ID,AreaID) '+@strsql execute sp_executesql @strsql execute sp_executesql @strsql2 --确定总记录数 declare @Counts int set @Counts = @@rowcount --确定总页数 IF @Counts%@iPageSize=0 SET @iPageCount=CEILING(@Counts/@iPageSize) ELSE SET @iPageCount=CEILING(@Counts/@iPageSize)+1 --若请求的页号大于总页数，则显示最后一页 IF @iPageIndex > @iPageCount SELECT @iPageIndex = @iPageCount --确定当前页的始末记录 DECLARE @iStart int --start record DECLARE @iEnd int --end record SELECT @iStart = (@iPageIndex - 1) * @iPageSize SELECT @iEnd = @iStart + @iPageSize + 1 --取当前页记录 --select ProID,ID,AreaID from #t where ProID>@iStart and ProID<@iEnd -- select * from #t where ID>300 declare @sql6 nvarchar(1000) set @sql6='select ProID,AreaID from #t where ProID>'+str(@iStart)+' and ProID<'+str(@iEnd) execute sp_executesql @sql6 set @CountsIndex =@@rowcount DROP TABLE #t return @Counts

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!