An efficient distributed stored procedure

Recently in a millions of data paged query, research a variety of programs, on this machine with the project's actual database to do the test, the test is very pain, hard to forget ing. Now the nonsense is not much to say, directly on the result, I believe this is also the most people search answers the most willing to see the way.

The following is the code for the stored procedure:

CREATE PROCEDURE [dbo]. [P_gridviewpager] (

@recordTotal int Ouput,--Total output records

@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),--Sort condition

@whereString varchar = ' 1=1 '--where condition

)

As

Begin

DECLARE @beginRow int

DECLARE @endRow int

DECLARE @tempLmit varchar (200)

declare @tempCount nvarchar (1000)

DECLARE @tempMain varchar (1000)

SET NOCOUNT ON

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 the total number of records

SET @tempCount = ' Select @recordTotal = COUNT (*) from (SELECT ' [e-mail protected]+ ' from ' [email protected]+ ' WHERE ' [email Protected]+ ') as My_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 () over (order by ' [email protected]+ ') as rows, ' [email protected]+ ' FRO M ' [email protected]+ ' WHERE ' [email protected]+ ') as Main_temp WHERE ' [email protected]

--print @tempMain

EXECUTE (@tempMain)

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

SET NOCOUNT off END

GO

An efficient distributed stored procedure (RPM)