因为数据有好几万条,所以要采用分页
用了AspnetPage分页控件,带不少查询条件,我看了下别人一般都是利用拼SQL语句来完成,考虑安全性能,要求不能拼SQL语句
代码见下--exec usp_Content_Search '',0,0,0,-1,0,0,'2007-01-01','2008-05-01',10,1
ALTER 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 --页数
)
AS
BEGIN
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
望指教
用了AspnetPage分页控件,带不少查询条件,我看了下别人一般都是利用拼SQL语句来完成,考虑安全性能,要求不能拼SQL语句
代码见下--exec usp_Content_Search '',0,0,0,-1,0,0,'2007-01-01','2008-05-01',10,1
ALTER 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 --页数
)
AS
BEGIN
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
望指教
解决方案 »
- .NET数据库的连接
- imgmap的使用案例
- CSDN 的BBS 那个提问列表是用的 gridview 等实现的吗?
- 请教一个在.net中用OWC11作图的问题
- 怎样判断客户端是否支持javascript和cookie和httpXML
- 想学习WEB编程,请教下如何入门,解答满意送分。
- vs.net 2005 的asp.net问题
- 初学ASP.net,求思路指导在一个网页上做一个表格。
- ASP.NET是用C#开发好好是VB.NET开发好一些.????
- repeater在计设时怎么无法拖动它到我想的位置?还有无法改变他的大小?
- .net连接informix乱码问题解决
- IE7客户端访问时 factory.printing 为空或不是对象,怎么办??在线等` `
取消这句获得的是全部的数据吧,该句是限定获得数据的条数。
SET ROWCOUNT 10
select * from tab where 1=1 and name=@name
使 SQL Server 在返回指定的行数之后停止处理查询。你应该使用Top来实现你的想法,这也是目前的常规做法.