Mssql efficient paging Stored Procedure sharing

Recently, I am doing a paging query of several million pieces of data. I have studied various solutions and tested it with the actual database of the project on the local machine. The testing process is very, which is so painful that I cannot look back at ing. Now, I don't want to talk much about it. I believe that this is the preferred way for most people who search for answers.

The following is the Stored Procedure Code:

Copy codeThe Code is as follows: create procedure [dbo]. [P_GridViewPager] (
@ RecordTotal int output, -- total number of OUTPUT records
@ ViewName VARCHAR (800), -- table name
@ FieldName VARCHAR (800) = '*', -- query a field
@ KeyName VARCHAR (200) = 'id', -- index Field
@ PageSize INT = 20, -- number of records per page
@ PageNo INT = 1, -- current page
@ OrderString VARCHAR (200), -- sorting Condition
@ WhereString VARCHAR (800) = '1 = 1' -- WHERE Condition
)
AS
BEGIN
DECLARE @ beginRow INT
DECLARE @ endRow INT
DECLARE @ tempLimit VARCHAR (200)
DECLARE @ tempCount NVARCHAR (1000)
DECLARE @ tempMain VARCHAR (1000)
-- Declare @ timediff datetime

Set nocount on
-- Select @ timediff = getdate () -- Record time

SET @ beginRow = (@ pageNo-1) * @ pageSize + 1
SET @ endRow = @ pageNo * @ pageSize
SET @ tempLimit = 'rows between' + CAST (@ beginRow as varchar) + 'and' + CAST (@ endRow as varchar)

-- The output parameter is the total number of records.
SET @ tempCount = 'select @ recordTotal = COUNT (*) FROM (SELECT '+ @ keyName + 'from' + @ viewName + 'where' + @ whereString +') AS my_temp'
EXECUTE sp_executesql @ tempCount, n' @ recordTotal INT output', @ recordTotal OUTPUT

-- Result set returned by the primary query
SET @ tempMain = 'select * FROM (SELECT ROW_NUMBER () OVER (order by '+ @ orderString +') AS rows, '+ @ fieldName + 'from' + @ viewName + 'where' + @ whereString +') AS main_temp WHERE '+ @ tempLimit

-- PRINT @ tempMain
EXECUTE (@ tempMain)
-- Select datediff (MS, @ timediff, getdate () as time consumed

Set nocount off
END

GO