62,041
社区成员
发帖
与我相关
我的任务
分享
--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