Stored Procedures Manual Paging SQL

CREATE procedure News_class

--Information News millions pagination

(

@StrWhere varchar,--conditions

@PageSize int,--page size

@PageIndex INT--page index

)

As

DECLARE @strSQL varchar (2000)--the subject sentence

DECLARE @strCountSQL varchar (2000)--general record subject sentence

DECLARE @strTmp varchar (1000)--Temporary variable

Set @strTmp = ' Select top ' + str (@PageSize) + ' title,addtime from Tb_news '--note here that several fields are required to read a few fields

If @StrWhere <> '

Begin

Set @[email protected] + ' where ID < (select min (ID) from (select top ' + str ((@PageIndex-1) * @PageSize) + ' ID from Tb_n EWS 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_n EWS 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

Stored Procedures Manual Paging SQL