/****** Object:  StoredProcedure [dbo].[Query_SerchResultList]    Script Date: 03/31/2012 15:22:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Query_SerchResultList](
    @type          INT,    --1微博/2用户/3微群
    @serchtag      VARCHAR(20),    --搜索关键词
    @pageindex     INT,    --页码
    @pagesize      INT,    --数据大小
    @isCount       INT --是否需要得到行数
)
AS
    DECLARE @sqlstr NVARCHAR(4000)
    DECLARE @taghave INT
    
    IF @isCount = 0
    BEGIN
        --搜索微博
        IF @type = 1
        BEGIN
            SET @sqlstr = 
                'SELECT *
            FROM   (
                       SELECT mb.ApplicationID,
                              mb.BlogContent,
                              mb.BlogUntreatedContent,
                              mb.BlogSender,
                              mb.BlogForward,
                              mb.BlogFrom,
                              mb.IsFirstPub,
                              mb.BlogTime,
                              mb.IsDeleted,
                              mb.CommentCount,
                              mb.ForwardCount,
                              mb.OriginalBlogID,
                              gi.GName,
                              gi.GId,
                              ROW_NUMBER() OVER(ORDER BY BlogTime DESC) AS Num
                       FROM   MicroBlog mb
                              LEFT OUTER JOIN GroupInfo gi
                                   ON  gi.GId = mb.GroupID
                       WHERE  mb.IsDeleted = 0
                              AND mb.BlogContent LIKE ''' + '%' + @serchtag +
                '%' +
                ''' 
                   )t1
            WHERE  t1.Num BETWEEN (' + CAST(@PageSize AS VARCHAR) + ' * (' +
                CAST(@PageIndex AS VARCHAR) + ' -1)) AND (' + CAST(@PageSize AS VARCHAR) 
                + ' * ' + CAST(@PageIndex AS VARCHAR) +
                ') 
            ORDER BY t1.BlogTime DESC';
        END
        
        --搜索用户
        IF @type = 2
        BEGIN
            SET @sqlstr = 'SELECT TOP ' + CAST(@pagesize AS VARCHAR) +
                ' 
                        a.UserID,a.NickName,a.MyDiscription,a.SelfPicture,a.ConcernedCount,a.ConcerningCount,b.validated
                        FROM UserInfo a, SysUser b 
                        Where a.NickName LIKE + ''' + '%' + @serchtag + '%' +
                '''  
                        AND a.UserID = b.UserID 
                        AND a.UserID
                        NOT IN  
                        (
                            SELECT TOP (' + CAST(@pagesize AS VARCHAR) + ' * (' 
                +
                CAST(@pageindex AS VARCHAR) +
                ' - 1)) a.UserID  
                            FROM UserInfo a, SysUser b 
                            Where a.NickName LIKE + ''' + '%' + @serchtag + '%' 
                +
                ''' 
                            AND a.UserID = b.UserID
                            ORDER BY BlogCount desc 
                        )
                        ORDER BY BlogCount desc';
        END
        
        --搜索微群
        IF @type = 3
        BEGIN
            SET @sqlstr = 'SELECT TOP ' + CAST(@pagesize AS VARCHAR) +
                ' 
                        a.GClassName,b.GId,b.GImage,b.GName,b.GSpeakSize,b.GUserSize,b.GDescription,b.GCreateDate,c.NickName,c.UserID
                        FROM GroupClass a,GroupInfo b,UserInfo c
                        WHERE a.GClassId = b.GGroupId
                        AND b.GUserid = c.UserID
                        AND b.GPrivate = 1
                        AND b.GName LIKE + ''' + '%' + @serchtag + '%' +
                ''' 
                        AND b.GId
                        NOT IN