The most efficient paging Query

Copy codeThe Code is as follows:
/*

Date: 2009-03-19
Function: obtains national game task list data based on various conditions.
*/
Create procedure [dbo]. [PrGs_Nation_Task_GetList]

@ PageSize int = 100, -- the number of records displayed on each page. The default value is 100.
@ PageIndex int = 1, -- the current page number to be displayed is extracted. The default value is 1. The database returns a page number based on PageSize and PageIndex calculation.

@ RetTotal int output, -- total number of records
@ RetCount int output, -- number of returned records
@ RetPageIndex int output, -- output the current page number
@ ReturnDesc varchar (128) output -- return operation result description
As
Begin

Set nocount on
Set xact_abort on

Set @ RetTotal = 0
Set @ RetCount = 0
Set @ RetPageIndex = @ PageIndex




-- Multi-condition Value
Declare @ Err int -- Error
Declare @ PageCount int -- total number of pages
Declare @ BeginRID int -- start row Rid
Declare @ MaxRow int -- last line

Select @ RetTotal = count (*)
From NationTask

Select @ Err =@@ ERROR

If @ Err <> 0
Begin
Set @ ReturnDesc = 'failed to extract the total number of national tasks! '
Return-1
End

-- If no data exists, an empty result set is returned.
If @ RetTotal = 0
Begin
Set @ ReturnDesc = 'No country task record for the current condition! '
Return 1
End

-- Calculate the total number of pages
Set @ PageCount = @ RetTotal/@ PageSize

If @ RetTotal % @ PageSize> 0
Begin
Set @ PageCount = @ PageCount + 1
End

-- If the total number of pages is exceeded, an empty result set is returned.
If @ PageIndex> @ PageCount
Begin
Set @ ReturnDesc = 'No country task record for the current condition! '
Return 1
End

-- Get the Rid of the first record of the page to be returned
Set @ MaxRow = @ PageSize * (@ PageIndex-1) + 1

Set rowcount @ MaxRow

Select @ BeginRID = Rid
From NationTask
Order by Rid desc


-- Return data list
Set rowcount @ PageSize

Select Rid
, TaskName
, TaskTitle
, ImageID
, EffectID
, StartTime
From NationTask
Where Rid <= @ BeginRID
Order by Rid desc

Set @ RetCount = @ rowcount

-- End
Set @ ReturnDesc = 'the national task list has been extracted! '
Return 1
End