SQL server tens of millions of data processing pages

* ***** Object: StoredProcedure [dbo]. [sp_GetEmployee] script date: 11/12/2009 14:10:29 ******/
Create PROCEDURE [dbo]. [sp_GetPageList]
@ TblName varchar (255), -- table name
@ StrGetFields varchar (1000) = '*', -- the column to be returned
@ FldName varchar (255) = '', -- Name of the sorted field
@ PageSize int = 10, -- page size
@ PageIndex int = 1, -- page number
@ OrderType bit = 0, -- set the sorting type. If the value is not 0, the sorting type is descending.
@ StrWhere varchar (1500) = ''-- query condition (note: Do not add where)
AS
Declare @ strSQL varchar (5000) -- subject sentence
Declare @ strTmp varchar (110) -- Temporary variable
Declare @ strOrder varchar (400) -- sort type

If @ OrderType! = 0
Begin
Set @ strTmp = '<(select min'
Set @ strOrder = 'Order by ['+ @ fldName +'] desc'
-- If @ OrderType is not 0, execute the descending order. This sentence is very important!
End
Else
Begin
Set @ strTmp = '> (select max'
Set @ strOrder = 'Order by ['+ @ fldName +'] asc'
End

If @ PageIndex = 1
Begin
IF @ strWhere! =''
SET @ strSQL = 'SELECT top '+ str (@ PageSize) + ''+ @ strGetFields + 'from [' + @ tblName + '] where' +
@ StrWhere + ''+ @ strOrder
ELSE
SET @ strSQL = 'SELECT top '+ str (@ PageSize) + ''+ @ strGetFields +' from ['+ @ tblName +'] '+ @ strOrder

-- Execute the above code on the first page, which will speed up the execution.
End
Else
Begin
-- The following code gives @ strSQL the SQL code to be actually executed
Set @ strSQL = 'SELECT top '+ str (@ PageSize) + ''+ @ strGetFields + 'from ['
+ @ TblName + '] where [' + @ fldName + ']' + @ strTmp + '([' + @ fldName + ']) from (select top' +
Str (@ PageIndex-1) * @ PageSize) + '[' + @ fldName + '] from [' + @ tblName + ']' + @ strOrder + ') as tblTmp) '+ @ strOrder

If @ strWhere! =''
Set @ strSQL = 'SELECT top '+ str (@ PageSize) + ''+ @ strGetFields + 'from ['
+ @ TblName + '] where [' + @ fldName + ']' + @ strTmp + '(['
+ @ FldName + ']) from (select top' + str (@ PageIndex-1) * @ PageSize) + '['
+ @ FldName + '] from [' + @ tblName + '] where' + @ strWhere +''
+ @ StrOrder + ') as tblTmp) and' + @ strWhere + ''+ @ strOrder
End

If @ strWhere! =''
SET @ strSQL = @ strSQL + '; Select count (*) AS Total FROM [' + @ tblName + '] where' + @ strWhere
Else
Set @ strSQL = @ strSQL + '; Select count (*) AS Total FROM [' + @ tblName + ']'

-- The above code indicates that if @ doCount is not passed over 0, the total number of statistics will be executed. All the code below is 0 @ doCount

Exec (@ strSQL)
Tip: The View + non-clustered index can be used for faster speeds. After testing, it can support processing tens of millions of data records.