Analysis of the asp.net paging Stored Procedure

Here we use the temporary performance of SQL to implement the database paging function. The benefits of this function can achieve mass data processing. For more information, see, if there is a better method for this method, please provide comments.

The Code is as follows:

Copy code

Alter procedure [dbo]. [sp_ SQL _Paging] ( @ SqlDataTable NVARCHAR (4000), -- table name @ PrimaryKey NVARCHAR (4000), -- primary key name @ Fields NVARCHAR (4000), -- the field to be returned @ PageSize INT, -- page size @ PageIndex INT, -- page number @ RecordCount int output, -- total number of records @ StrOrderBy NVARCHAR (4000), -- Sort @ StrWhere NVARCHAR (4000) -- Query Condition ) AS BEGIN SET NOCOUNT ON DECLARE @ strSQL1 NVARCHAR (4000) -- SQL statement 1 DECLARE @ strSQL2 NVARCHAR (4000) -- SQL statement 2 DECLARE @ strSQL3 NVARCHAR (4000) -- SQL statement 3 SET @ strSQL1 = 'select' + @ PrimaryKey + ', ROW_NUMBER () OVER (' + @ strOrderBy + ') AS RowNumber FROM' + @ SqlDataTable + ''+ @ strWhere -- Get the total number of records SET @ strSQL3 = 'select @ recordCount = COUNT (*) FROM '+ @ SqlDataTable + ''+ @ StrWhere EXEC SP_EXECUTESQL @ Stmt = @ strSQL3, @ Params = n' @ recordCount as int output ', @ RecordCount = @ recordCount OUTPUT -- Paging Query IF @ pageIndex> @ recordCount * 1.0/@ pageSize + 1.0 OR @ recordCount <= @ pageSize BEGIN SET @ pageIndex = 1 END SET @ strSQL2 = 'select' + @ Fields + 'from' + @ SqlDataTable + 'where' + @ PrimaryKey + 'in (SELECT '+ @ PrimaryKey +' FROM ('+ @ strSQL1 +') TempTable WHERE RowNumber BETWEEN '+ Str (@ pageIndex-1) * @ pageSize + 1) + 'and' + Str (@ PageIndex * @ pageSize) + ')' + @ strOrderBy EXEC SP_EXECUTESQL @ strSQL2 END