Two SQL paging stored procedure Code

The article has a collection of two SQL paging stored procedure Code, these two stored procedures are two efficient paging stored procedure Code, if you think you write the statement is not enough, strong, you can use our ready-made efficient paging stored procedure instance code.

CREATE PROCEDURE Pages
@tablenames varchar (200)--table name, can be multiple tables, but cannot be aliased
@primarykey varchar (100),--primary key, can be null, but @order is empty when the value cannot be null
@fields varchar (800),--the field to be fetched, can be a field of more than one table, can be empty, empty to represent a select *
@pagesize int,--Number of records per page
@currentpage int,--current page, 0 represents page 1th
@filter varchar (200) = ',--condition, can be empty without filling in where
@order varchar (200) = '---sort, can be empty, default by primary key in ascending order, not filled in by
As
Begin
DECLARE @toprow varchar (12)
DECLARE @temppagesize varchar (12)
if (Len (@order) >0)
Begin
Set @order = ' ORDER BY ' + @order
End
Else
Begin
Set @order = '
End
if (Len (@filter) <1)
Begin
Set @filter = ' 1=1 '
End
if (@currentpage -1<=0)
Set @currentpage =0
Set @toprow = RTrim (LTrim (str (@pagesize * (@currentpage-1)))
Set @temppagesize = RTrim (LTrim (str (@pagesize))
EXEC ('
Declare @temptable table (rownum int identity (1,1), GID varchar (12)) ' + '
Declare @datatable table (GID varchar (12)) ' + '
DECLARE @timer datetime ' + '
Set @timer = getdate () ' + '
SET NOCOUNT ON ' + '
Insert into @temptable (GID) Select ' + @primarykey + ' from ' + @tablenames + ' where ' + @filter + @order + '
Insert into @datatable (GID) Select top ' + @temppagesize + ' gid from @temptable where rownum> ' + @toprow + '
SET ROWCOUNT ' + @temppagesize + '

Select ' + @fields + ' from ' + @tablenames + ' where ' + @filter + ' and ' + @primarykey + ' in (select GID from @datatable) ' + @order + '
SET ROWCOUNT 0 ' + '
Print (DateDiff (millisecond, @timer, GETDATE ()))
Print (' INSERT into @temptable (GID) Select ' + @primarykey + ' from ' + @tablenames + ' where ' + @filter + @order)
Print (' select ' + @fields + ' from ' + @tablenames + ' where ' + @filter + ' and ' + @primarykey + ' (select gid from @datatable) ' + @ord Er
End
Go

Instance Code two

ALTER procedure [dbo]. [Common_procedure_selectwithpage]
@sql varchar (5000),
@currentpageno int,
@pagesize int,
@totalnum int Output
As
SET NOCOUNT ON
DECLARE @sqlcmd varchar (5000)
--------------------------------------------Query data
Set @sqlcmd = ' select * FROM (' + @sql + ') a where rowindex between ' + CONVERT (varchar, (@currentpageno-1) * @pagesize + 1) + ' + ' + convert (varchar, @currentpageno * @pagesize)
EXEC (@sqlcmd) print (@sqlcmd)
--------------------------------------------Total records
If @totalnum =-1
Begin
CREATE TABLE #temp1 (num int)
INSERT INTO #temp1
EXEC (' SELECT COUNT (*) from (' + @sql + ') a ')
Select @totalnum = (SELECT * from #temp1)
drop table #temp1
End

The usage is simple, but you must use the Row_number () over (...) as rowindex in the incoming SQL:
DECLARE @sql varchar (5000)
DECLARE @currentpageno int
DECLARE @pagesize int
DECLARE @totalnum int

Set @currentpageno = 100
Set @pagesize = 10
Set @totalnum =-1
Set @sql = ' select *, Row_number () over (ordered by sort field) as rowindex from table name A with (NOLOCK) '

exec [dbo]. [Common_procedure_selectwithpage] @sql, @currentpageno, @pagesize, @totalnum output

Select @totalnum