MSSQL efficient paging storage process sharing _mssql

Recently in doing a millions of data paging query, studied a variety of scenarios, on the machine with the actual project database to do the test, testing process is very pain, forget ing. Now the nonsense is not much to say, directly on the results, I believe this is also the most people search the answer the most willing to see the way.

The following is the code for the stored procedure:

Copy Code code as follows:

CREATE PROCEDURE [dbo]. [P_gridviewpager] (
@recordTotal INT Output--total number of outputs recorded
@viewName VARCHAR (800),--table name
@fieldName VARCHAR (800) = ' * ',--query field
@keyName VARCHAR = ' Id ',--index field
@pageSize INT = 20,--Number of records per page
@pageNo INT = 1,--current page
@orderString VARCHAR (200),--Sorting criteria
@whereString VARCHAR = ' 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 ()--recording time

SET @beginRow = (@pageNo-1) * @pageSize + 1
SET @endRow = @pageNo * @pageSize
SET @tempLimit = ' rows BETWEEN ' + CAST (@beginRow as VARCHAR) + ' and ' +cast (@endRow as VARCHAR)

--Output parameter is total record number
SET @tempCount = ' Select @recordTotal = COUNT (*) from (SELECT ' + @keyName + ' from ' + @viewName + ' WHERE ' + @whereString + ') as M Y_temp '
EXECUTE sp_executesql @tempCount, N ' @recordTotal INT output ', @recordTotal output

--The main query returns the result set
SET @tempMain = ' SELECT * FROM (select Row_number () + @orderString + ") as rows, ' + @fieldName + ' from ' + @viewN Ame+ ' where ' + @whereString + ') as Main_temp WHERE ' + @tempLimit

--print @tempMain
EXECUTE (@tempMain)
--select DateDiff (MS, @timediff, GETDATE ()) as time consuming

SET NOCOUNT OFF
End

Go