預存程序手工分頁sql

標籤：

CREATE procedure News_Class

--資訊新聞百萬級分頁

(

@StrWhere varchar(100),--條件

@PageSize int, --頁面大小

@PageIndex int --頁面索引

)

AS

declare @strSQL varchar(2000) -- 主語句

declare @strCountSQL varchar(2000) -- 總記錄主語句

declare @strTmp varchar(1000) -- 臨時變數

Set @strTmp =‘ Select top ‘+ str(@PageSize) + ‘ Title,AddTime from Tb_News ‘ --此處注意，需幾個欄位讀幾個欄位

if @StrWhere<>‘‘

Begin

Set @[email protected] + ‘ where ID < (select min(ID) from (select top ‘ + str((@PageIndex-1)*@PageSize)+‘ ID from Tb_News Where ‘[email protected]+‘ order by ID desc) as tblTmp ) and ‘[email protected]+‘ order by ID desc‘

set @strCountSQL=‘select count(ID) as countx from Tb_News Where ‘[email protected]+‘ ‘

End

else

Begin

Set @[email protected] + ‘ where ID < (select min(ID) from (select top ‘ + str((@PageIndex-1)*@PageSize)+‘ ID from Tb_News order by ID desc) as tblTmp ) order by ID desc‘

set @strCountSQL=‘select count(ID) as countx from Tb_News ‘

End

if @PageIndex = 1

if @StrWhere<>‘‘

Begin

Set @[email protected] +‘ Where ‘[email protected]+‘order by ID desc‘

End

else

Begin

Set @[email protected] +‘order by ID desc‘

End

exec (@strSQL)

exec (@strCountSQL)

GO

預存程序手工分頁sql