因为数据有好几万条，所以要采用分页
用了AspnetPage分页控件，带不少查询条件，我看了下别人一般都是利用拼SQL语句来完成，考虑安全性能，要求不能拼SQL语句
代码见下
- SQL code
--exec usp_Content_Search '',0,0,0,-1,0,0,'2007-01-01','2008-05-01',10,1ALTER PROCEDURE usp_Content_Search (@Title VARCHAR(500),@DepartmentId INT, --部门编号@ItemId INT, --栏目编号@Positions INT, --位置@Status INT, --状态@RoleId INT, --角色@UserId INT, --用户ID@StartTime DATETIME,@EndTime DATETIME,@PageSize INT, --每页显示的数据@PageIndex INT --页数)ASBEGIN CREATE TABLE #indextable (id INT IDENTITY(1,1),nid INT,npositions INT ,branchid INT,nitemid INT,nstatus INT,nuserid INT) CREATE TABLE #temp(id INT IDENTITY(1,1),nid INT,) SET NOCOUNT ON DECLARE @PageLowerBound INT DECLARE @PageUpperBound INT SET @PageLowerBound=0 SET @PageUpperBound=0 SET @PageLowerBound =(@PageIndex-1)*@PageSize SET @PageUpperBound =@PageLowerBound+@PageSize SET ROWCOUNT @pageUpperBound DELETE FROM [#indextable] INSERT INTO [#indextable](nid,npositions,branchid,nitemid,nstatus,nuserid) SELECT id,[Positions],[DepartmentId],[ItemId],status,[UserId] FROM [Content] WHERE Status<>99 AND [Title] LIKE '%'+@Title+'%' AND (DATEDIFF(s,PublishedTime,@EndTime)>0) AND (DATEDIFF(s,@StartTime,PublishedTime)>0) ORDER BY id DESC IF(@DepartmentId<>0) BEGIN DELETE FROM #indextable WHERE branchId<>@departmentId END IF(@ItemId<>0) BEGIN DELETE FROM #indextable WHERE [nitemid]<>@Itemid END IF(@UserId<>0) BEGIN DELETE FROM #indextable WHERE [nuserid]<>@UserId END IF(@Status<>-1) BEGIN DELETE FROM #indextable WHERE [nstatus]<>@Status END IF(@Positions<>99) BEGIN DELETE FROM [#indextable] WHERE [npositions]<>@Positions END IF(@RoleId>1) BEGIN IF(@RoleId>3) BEGIN DELETE FROM #indextable WHERE [nuserid]<>@UserId END ELSE BEGIN DELETE FROM #indextable WHERE [branchid]<>@DepartmentId END END SET NOCOUNT OFF INSERT INTO [#temp] ([nid]) SELECT [nid] FROM [#indextable] SELECT C.*,I.ItemName,U.[RealName] FROM Content c,Item I,[Users] U,#temp t where C.[ItemId] = I.[ItemId] and C.UserId = U.[UserId] and C.Id=t.nid and t.id > @PageLowerBound AND t.id<= @PageUpperBound order by C.PublishedTime desc END
现在就是查询出来的数据不对，如果PageSize为10的话，数据库前10条没有符合条件的数据就查不出来
按要求来说应该是查询出符合条件的前10条啊，先查询，再取前10条，现在是先取再查询了
好像是SET ROWCOUNT @pageUpperBound这一句的问题，如果取消这句，分页速度会影响不？对这句不是很理解
用的是SQL2000
望指教
------解决方案--------------------------------------------------------
SET ROWCOUNT @pageUpperBound
取消这句获得的是全部的数据吧，该句是限定获得数据的条数。
------解决方案--------------------------------------------------------
up一下 拼也没有什么大不了了 做好过滤就ok了
------解决方案--------------------------------------------------------
限定条件后取得是10条可以的
SET ROWCOUNT 10
select * from tab where 1=1 and name=@name
------解决方案--------------------------------------------------------
感觉你这样太麻烦了吧,不如用游标呢.
------解决方案--------------------------------------------------------
------解决方案--------------------------------------------------------