--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