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,NewsNameSi,ContentSi,ManNum,EndDate,SmallPic,SortID,SortPath,ClickNumber,AddTime,IsOk,xuhao,Force,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,NewsNameSi,ContentSi,ManNum,EndDate,SmallPic,SortID,SortPath,ClickNumber,AddTime,IsOk,xuhao,Force,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,NewsNameSi,SortID,SortPath,SmallPic,ContentSi,ManNum,EndDate,ClickNumber,AddTime,IsOk,xuhao,Force,AreaID from #t where ProID>@iStart and ProID<@iEnd -- select * from #t where ID>300 declare @sql6 nvarchar(1000) set @sql6='select ProID,ID,NewsNameSi,SortID,SortPath,SmallPic,ContentSi,ManNum,EndDate,ClickNumber,AddTime,IsOk,xuhao,Force,AreaID from #t where ProID>'+str(@iStart)+' and ProID<'+str(@iEnd) execute sp_executesql @sql6 set @CountsIndex =@@rowcount DROP TABLE #t return @Counts