根据关键字搜索的一个存贮过程

 

CREATE          PROCEDURE UP_FeedBack_Comment_KeyWord_Paged
@PageIndex INT, --开始页号
@PageSize INT,  --页数大小
@Keyword varchar(200),  --搜索关键字
@RecordCount INT OUT, --总记录数
@PageCount INT OUT --总页数
AS

SELECT @RecordCount = COUNT(*) FROM BZ_Data_Feedback  WHERE Context LIKE '%' + @Keyword + '%'

SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)

DECLARE @SQLSTR NVARCHAR(1000)

IF @PageIndex = 0 OR @PageCount <= 1

SET @SQLSTR ='SELECT TOP '+STR( @PageSize )+' Feedback_ID, Context,SaveTime,FeedBack_Type FROM BZ_Data_Feedback WHERE Context LIKE ''%'+ @Keyword +'%'' ORDER BY Feedback_ID DESC'

ELSE IF @PageIndex = @PageCount - 1

SET @SQLSTR ='SELECT * FROM ( SELECT TOP '+STR( @RecordCount - @PageSize * @PageIndex )+' Feedback_ID, Context,SaveTime,FeedBack_Type FROM BZ_Data_Feedback WHERE Context LIKE ''%' + @Keyword + '%'' ORDER BY Feedback_ID ASC ) TempTable ORDER BY Feedback_ID DESC'

ELSE

SET @SQLSTR =' SELECT TOP '+STR( @PageSize )+' * FROM ( SELECT TOP '+STR( @RecordCount - @PageSize * @PageIndex )+' Feedback_ID, Context,SaveTime,FeedBack_Type FROM BZ_Data_Feedback WHERE Context LIKE ''%'+ @Keyword + '%'' ORDER BY Feedback_ID ASC ) TempTable ORDER BY Feedback_ID DESC'

EXEC (@SQLSTR)

GO

***********************2****************************
CREATE      PROCEDURE UP_FeedBack_Comment_Paged
@PageIndex INT, --????
@PageSize INT,  --????
@Feedback_id  int, --??ID
@RecordCount INT OUT, --????
@PageCount INT OUT --???
AS
SELECT @RecordCount = COUNT(*) FROM BZ_Data_Feedback_Comment WHERE Feedback_ID=@Feedback_id
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
DECLARE @SQLSTR NVARCHAR(1000)
IF (@PageIndex = 0 OR @PageCount <= 1)
begin
SET @SQLSTR =' SELECT TOP  ' +   convert(varchar(5),@PageSize) + ' Feedback_Comment_ID,Feedback_ID,Context, SaveTime  FROM BZ_Data_Feedback_Comment  WHERE Feedback_ID= '+convert(varchar(18),@Feedback_id)+'  ORDER BY Feedback_Comment_ID DESC '
end
ELSE IF (@PageIndex = @PageCount - 1 )
begin
SET @SQLSTR =' SELECT * FROM ( SELECT TOP '+convert(varchar(5),( @RecordCount - @PageSize * @PageIndex ))+' Feedback_Comment_ID, Feedback_ID, Context,SaveTime FROM BZ_Data_Feedback_Comment WHERE Feedback_ID=  ' +convert(varchar(18), @Feedback_id) + '  ORDER BY Feedback_Comment_ID ASC ) TempTable ORDER BY Feedback_Comment_ID DESC'
end
ELSE
SET @SQLSTR =' SELECT TOP '+convert(varchar(5), @PageSize )+' * FROM ( SELECT TOP '+STR( @RecordCount - @PageSize * @PageIndex )+'Feedback_Comment_ID, Feedback_ID, Context,SaveTime FROM BZ_Data_Feedback_Comment WHERE Feedback_ID= ' + convert(varchar(18),@Feedback_id )+ '  ORDER BY Feedback_Comment_ID ASC ) TempTable ORDER BY Feedback_Comment_ID DESC'
EXEC (@SQLSTR)
GO