Paging SQL Server stored procedure
Paging SQL Server stored procedure
/* -- Paging program implemented by stored procedures
Displays page X of the specified table, view, and query result.
In the case of primary keys or ID columns in the table, query the data directly from the original table. In other cases, use the temporary table method.
This method is not recommended if the view or query result contains a primary key.
-- Producer build 2003.09 --*/
/* -- Call example
Exec p_show 'region information'
Exec p_show 'region information', 5, 3, 'region no., region name, mnemonic code ', 'region no'
--*/
/*
The query statements with sorting must be sorted first and then output the result. that is:
Exec p_show 'Select top 100 percent * from Region Data order by region name', 'region number, region name, mnemonic code ', 'region name'
-- Query statement Plus: top 100 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, -- size of each page (number of rows)
@ PageCurrent int = 1, -- the page to be displayed
@ FdShow nvarchar (4000) = '', -- List of fields to be displayed. if the query result contains an ID field, you need to specify this value without the ID field.
@ FdOrder nvarchar (1000) = ''-- List of sorting fields
As
Declare @ FdName nvarchar (250) -- Primary key or column name in the table or temporary table
, @ Id1 varchar (20), @ Id2 varchar (20) -- start and end record numbers
, @ Obj_ID int -- object ID
-- Processing of compound primary keys in the table
Declare @ strfd nvarchar (2000) -- composite primary key list
, @ Strjoin nvarchar (4000) -- Connection field
, @ Strwhere nvarchar (2000) -- query condition
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 is not null then ''+ @ QueryStr else '(' + @ QueryStr + ') a' end
-- If the first page is displayed, top can be used directly.
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 whether there is an identifier or a 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 the table has no ID column, check whether the table has a primary key.
Begin
If not exists (select 1 from sysobjects where parent_obj = @ Obj_ID and xtype = 'PK ')
Goto lbusetemp -- if the table has no primary key, use a temporary table for processing.
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)
, @ Strwhere = substring (@ strwhere, 5)
Goto lbusepk
End
End
End
Else
Goto lbusetemp
/* -- Use the identification column or primary key as a single field --*/
Lbuseidentity:
Exec ('select top '+ @ Id1 + @ FdShow + 'from' + @ QueryStr
+ 'Where' + @ FdName + 'not in (select top'
+ @ Id2 + ''+ @ FdName + 'from' + @ QueryStr + @ FdOrder
+ ')' + @ FdOrder
)
Return
/* -- The table has a composite primary key --*/
Lbusepk:
Exec ('select' + @ FdShow + 'from (select top' + @ Id1 + 'A. * from
(Select top 100 percent * from '+ @ QueryStr + @ FdOrder +')
Left join (select top '+ @ Id2 + ''+ @ strfd +'
From '+ @ QueryStr + @ FdOrder +') B on '+ @ strjoin +'
Where '+ @ strwhere +')'
)
Return
/* -- Use a 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 + 'beten'
+ @ Id1 + 'and' + @ Id2
)
GO
