Efficient paging method code (SQL millions data paging code)

Efficient paging method code (SQL millions data paging code)

@querystr nvarchar,--table name, view name, query statement
@pagesize int=10,--the size (number of rows) per page
@pagecurrent int=1,--the page to display
@fdshow nvarchar = ',--the list of fields to display, if the query results are marked with a literacy section, you need to refer to

This value, and does not contain an identity field
@fdorder nvarchar = ',--sort field List
@wherestr nvarchar (200) = ',-content is ' id=3 and model_no like '%24% '

and '
@rscount int=0 Output
As
Set @fdshow = ' + @fdshow + '
Set @fdorder = ' + @fdorder + '
Set @wherestr = ' + @wherestr + '

declare @fdname nvarchar (250)--An identity column name in a primary key or table in a table, in a temporary table
, @id1 varchar, @id2 varchar (20)--start and end record numbers
, @obj_id int--Object ID
, @temp nvarchar (300)--temporary statement
, @strparam nvarchar (100)--Temporary parameters

declare @strfd nvarchar (2000)--Composite primary Key list
, @strjoin nvarchar (4000)--Connection field
, @strwhere nvarchar (2000)--Query criteria
--Check input parameters
Set @querystr =ltrim (RTrim (@querystr))
Select @obj_id =object_id (@querystr)
, @fdshow =case isnull (@fdshow, ') when ' then ' * ' Else ' + @fdshow end
, @fdorder =case isnull (@fdorder, ') when ' then ' ' Else ' ORDER by

' + @fdorder End
, @querystr =case when @obj_id be not null then ' + @querystr Else '

(' + @querystr + ') A ' end
--output total number of records
Set @temp = ' Select @rscount =count (*)   from ' + @querystr + ' + @wherestr
Set @strparam = n ' @rscount int out '
Execute sp_executesql  @temp, @strparam, @rscount out
--if the first page is displayed, you can use the top directly Complete the
if @pagecurrent =1
begin
Select @id1 =cast (@pagesize as varchar)
Exec (' select top ' + @id1 + @fdshow + ' F Rom ' + @querystr + @wherestr + @fdorder '
return
End
--if it is a table, check that the table has an identity or primary key
If @obj_id is not null and Objectpro Perty (@obj_id, ' istable ') =1
begin
Select @id1 =cast (@pagesize as varchar)
, @id2 =cast ((@pagecurrent-1) * @pagesize as varchar)
Select @fdname =name from syscolumns where id= @obj_id and status=0x80
if @ @rowcount =0--if Table does not have an identity column, check the table for a primary key
begin
If not exists (select 1 from sysobjects where parent_obj= @obj_id and

Xtype= ' PK ')
Goto lbusetemp--If there are no primary keys in the table, the temporary table handles
Select @fdname =name from syscolumns where id= @obj_id and colid in (
Select Colid from Sysindexkeys where @obj_id =id and indid in (
Select Indid from sysindexes where @obj_id =id and name in (
Select name from sysobjects where xtype= ' PK ' and parent_obj= @obj_id
)))

If @ @rowcount >1--check whether the primary key in the table is a composite primary key
Begin
Select @strfd = ', @strjoin = ', @strwhere = '
Select @strfd = @strfd + ', [' +name+ '] '
, @strjoin = @strjoin + ' and a.[' +name+ ']=b.[' +name+ '] '
, @strwhere = @strwhere + ' and b.[' +name+ ' is null '
From syscolumns where id= @obj_id and colid in (
Select Colid from Sysindexkeys where @obj_id =id and indid in (
Select Indid from sysindexes where @obj_id =id and name in (
Select name from sysobjects where xtype= ' PK ' and parent_obj= @obj_id
)))
Select @strfd =substring (@strfd, 2,2000)
, @strjoin =substring (@strjoin, 5,4000)
, @strwhere =substring (@strwhere, 5,4000)
Goto LBUSEPK
End
End
End
Else
Goto Lbusetemp
/*--use an identity column or primary key as a single field processing method--*/
Lbuseidentity:
If Len (@wherestr) >10
Begin
EXEC (' select top ' + @id1 + @fdshow + ' from ' + @querystr
+ @wherestr + ' and ' + @fdname + ' not in (select Top ')
+ @id2 + ' + @fdname + ' from ' + @querystr + @wherestr + @fdorder
+ ') ' + @fdorder
)
Return
End
Else
Begin
EXEC (' select top ' + @id1 + @fdshow + ' from ' + @querystr
+ ' where ' + @fdname + ' not in ' (select Top ')
+ @id2 + ' + @fdname + ' from ' + @querystr + @fdorder
+ ') ' + @fdorder
)
Return
End
The processing method of compound primary key in/*--table--*/
LBUSEPK:
EXEC (' select ' + @fdshow + ' from (select top ' + @id1 + ' a.* from
(select top percent * from ' + @querystr + @fdorder + ') a
Left join (select top ' + @id2 + ' + @strfd + ')
From ' + @querystr + @fdorder + '] B on ' + @strjoin + '
where ' + @strwhere + ') a '
)
Return
The method of/*--with temporary table--*/
Lbusetemp:
Select @fdname = ' [id_ ' +cast (NEWID () as varchar (40)) + '] '
, @id1 =cast (@pagesize * (@pagecurrent-1) as varchar (20))
, @id2 =cast (@pagesize * @pagecurrent-1 as varchar (20))
EXEC (' select ' + @fdname + ' =identity (int,0,1), ' + @fdshow + '
into #tb from ' + @querystr + @fdorder + '
Select ' + @fdshow + ' from #tb where ' + @fdname + ' between '
+ @id1 + ' and ' + @id2
)