CREATE PROCEDURE [up_Announcement_GetPaged]

@pagesize int, --每頁顯示的記錄數

@pageindex int, --當前頁索引,最小值為1

@RecordCount int output, --總記錄數，<0時不統計結果記錄

@txtKeyWords NVARCHAR(50),

@IsRelease CHAR(1)

AS

SET NOCOUNT ON

DECLARE @indextable TABLE(rowid INT IDENTITY(1,1),nid INT)

INSERT INTO @indextable(nid) --將符合的記錄插入到臨時表中

SELECT [ID]

FROM [Announcement] WHERE (Title like "%"+@txtKeyWords+"%" OR Content like "%"+@txtKeyWords+"%")

AND (IsRelease=CASE @IsRelease WHEN "2" THEN IsRelease ELSE @IsRelease END)

ORDER BY [OrderID],AddDate DESC,ReleaseDate DESC

IF(@RecordCount<0)

BEGIN

SELECT @RecordCount= Count(1) FROM @indextable

END

DECLARE @PageLowerBound INT

DECLARE @PageUpperBound INT

SET @PageLowerBound=(@pageindex-1)*@pagesize

SET @PageUpperBound=@PageLowerBound+@pagesize

SET ROWCOUNT @PageUpperBound--最多執行行數 若下面還有超過此值的行數，請註釋此行

SELECT a.*

FROM [Announcement] a,@indextable t

WHERE a.[ID]=t.nid and t.rowid>@PageLowerBound and t.rowid<=@PageUpperBound

ORDER BY t.rowid

SET NOCOUNT OFF

GO