SQL Server uses the RowNumber () built-in function with the over keyword to implement a common paging stored procedure (supports single-or multiple-table node-search-Set paging)

Original: SQL Server uses RowNumber () built-in functions with the over keyword to implement a common paging stored procedure (support for single-or multiple-table node-search-Sets paging)

SQL Server uses the RowNumber () built-in function with the over keyword to implement a common paging stored procedure that supports single-or multiple-table-node-search-Set paging with the following stored procedures:

/******************/--author: Dream On the Journey (www. zuowenjun.cn)--createdate:2015-06-02--function: Paging gets data/******************/create procedure [dbo]. [Sp_datapaging] (@selectsqlnvarchar),--query field SQL, without select, to support flexible notation, such as: Col1,col3,isnull (Col4, ") as Col4@fromsqlnvarchar,-- query table and conditional SQL, without from, if included conditions, add where, such as: table where col1= ' test ' @orderbysql nvarchar,--query sort SQL, excluding order by, such as: ID ORDER BY Desc@pagesizeint=20,--Show the number of records per page @pagenoint=1,--the current query page number, starting with 1 @returnrecordcount bit=1--whether the total number of records need to be returned, or two result tables if set to 1 asbegindeclare @sqlcount nvarchar, @sqlstring nvarchar (max) set @sqlstring =n ' from (select Row_number () over (order By ' + @orderbysql + n ') as rowId, ' + @selectsql + n ' from ' + @fromsql +n ') as T ' if (@returnrecordcount =1) Beginset @sqlcoun T=n ' select count (rowId) as Resultcount ' + @sqlstringexec (@sqlcount) endset @sqlstring =n ' select * ' + @sqlstring + ' where RowId between ' + convert (nvarchar), (@pageno-1) * @pagesize + 1) + ' and ' +convert (nvarchar (), @pageno * @pagesize) EXEC ( @sqlstring) End

Use the following:

--single-table query exec [dbo]. [Sp_datapaging] ' * ', ' assetdetail ', ' Assetsingleno ', 10,1--multi-table query exec [dbo]. [Sp_datapaging] ' A.* ', ' Inventory a left joins Assetdetail B on A.storeno=b.storeno and A.companyid=b.companyidinner joins Asset C on B.asset Id=c.id and B.companyid=c.companyid ', ' a.id ', 20,3,1

The results are shown as follows (for example, multiple table queries):

SQL Server uses the RowNumber () built-in function with the over keyword to implement a common paging stored procedure (supports single-or multiple-table node-search-Set paging)