Paging SQL Server stored procedure _mssql

/*--a paging program implemented using stored procedures
Show page x for specified tables, views, query results
For the primary key or identity column in the table, the query is taken directly from the original table, and other cases use the temporary table method
This method is not recommended if there are primary keys in the view or query results
--Jiangjian 2003.09--*/
/*--Call Example
EXEC p_show ' Area information '
EXEC p_show ' area information ', 5, 3, ' region number, area name, mnemonic code ', ' area number '
--*/
/*
Because of the universality, there is a certain requirement for the query with ordering. If you sort first, then the result.
EXEC p_show ' Select top percent * from region data order by region name ', 5, 3, ' region number, region name, mnemonics ', ' Region name '
--query statement plus: Top percent//top
*/
if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ P_show] and OBJECTPROPERTY (ID, N ' isprocedure ') = 1)
drop procedure [dbo]. [P_show]
Go
Create Proc P_show
@QueryStr nvarchar (4000),--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 (4000) = ',--list of fields to display, if the query results have a read-only paragraph, you need to specify this value and do not include an identity field
@FdOrder nvarchar (1000) = '--sorted field list
As
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
--processing of compound primary key in table
declare @strfd nvarchar (2000)--Composite primary Key list
, @strjoin nvarchar (4000)--Connection field
, @strwhere nvarchar (2000)--Query criteria

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
--If the first page is displayed, you can do it directly with top
If @PageCurrent =1
Begin
Select @Id1 =cast (@PageSize as varchar (20))
EXEC (' select top ' + @Id1 + @FdShow + ' from ' + @QueryStr + @FdOrder)
Return
End
--if it is a table, check to see if there is an identity or primary key in the table
If @Obj_ID is not null and OBJECTPROPERTY (@Obj_ID, ' istable ') =1
Begin
Select @Id1 =cast (@PageSize as varchar (20))
, @Id2 =cast ((@PageCurrent-1) * @PageSize as varchar (20))
Select @FdName =name from syscolumns where id= @Obj_ID and status=0x80
If @ @rowcount = 0--If there are no identity columns in the table, check the table for primary keys
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:
EXEC (' select top ' + @Id1 + @FdShow + ' from ' + @QueryStr
+ ' where ' + @FdName + ' not in ' (select Top ')
+ @Id2 + ' + @FdName + ' from ' + @QueryStr + @FdOrder
+ ') ' + @FdOrder
)
Return
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
)
Go