General efficient paging Stored Procedure

Using Stored Procedures is indeed much more efficient than recordset. See the following example:

-- Get data on a specified page
Create procedure DBO. pagination

@ 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
@ Docount bit = 0, -- returns the total number of records. If the value is not 0, the system returns
@ 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 @ docount! = 0
Begin
If @ strwhere! =''
Set @ strsql = 'select count (*) as total from ['+ @ tblname +'] where' + @ strwhere
Else
Set @ strsql = 'select count (*) as total from ['+ @ tblname +']'
End
-- AboveCodeIt means that if @ docount is not passed over 0, the total number of statistics will be executed. All the code below is 0 @ docount

Else
Begin
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 grants @ 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
exec (@ strsql)
go