Solutions for SQL Server-based general paging stored procedures

There are many paging methods for SQL Server 0.1 million. Is it more efficient? It will be difficult to reach the level or above. This is not an example here.

Row_number is also a major improvement over SQL server2005. For details, refer

Copy codeThe Code is as follows: SQL Server2005 General paging storage process

Create procedure [dbo]. [Common_GetPagedList]
(
@ TableName nvarchar (100), -- table name
@ ColumnNames nvarchar (1000) = '*', -- a set of Field Names (all fields are *, and other fields are separated by commas)
@ OrderClause nvarchar (1000), -- Sort clause (excluding order)
@ WhereClause nvarchar (1000) = n' 1 = 1', -- Condition Clause (excluding where)
@ PageSize int = 0, -- number of records per page (0 indicates all records)
@ PageIndex int = 1, -- page index (starting from 1)
@ TotalRecord int output -- total number of returned records
)
AS

BEGIN
If (@ ColumnNames is null or @ ColumnNames = '') set @ ColumnNames = '*'
If (@ WhereClause is null or @ WhereClause = '') set @ WhereClause = '1 = 1'
If (@ OrderClause is null or @ OrderClause = '') set @ OrderClause = 'id desc'

-- Process start and end points
Declare @ StartRecord int;
Declare @ EndRecord int;
Declare @ TotalCountSql nvarchar (1200 );
Declare @ SqlString nvarchar (4000 );

-- Statistical records
If (@ TotalRecord is null OR @ TotalRecord> = 0)
Begin
SET @ TotalCountSql = n' select @ TotalRecord = count (*) from '+ @ TableName + 'where' + @ WhereClause;
-- Select @ TotalCountSql
EXEC sp_executesql @ totalCountSql, n' @ TotalRecord int out', @ TotalRecord output; -- total number of records returned
End

If @ PageSize> 0
Begin
If @ PageIndex <1 set @ PageIndex = 1
Set @ StartRecord = (@ PageIndex-1) * @ PageSize + 1
Set @ EndRecord = @ StartRecord + @ PageSize-1
Set @ SqlString = n' select row_number () over (order by '+ @ OrderClause +') as rowId, '+ @ ColumnNames + 'from' + @ TableName + 'where' + @ WhereClause;
Set @ SqlString = 'select * from ('+ @ SqlString +') as t where rowId between '+ ltrim (str (@ StartRecord )) + 'and' + ltrim (str (@ EndRecord ));
End
Else
Begin
Set @ SqlString = 'select' + @ ColumnNames + 'from' + @ TableName + 'where' + @ WhereClause + 'ORDER BY' + @ OrderClause
End
-- Select @ SqlString
Exec (@ SqlString)
END