Create Proc P_show
@Querystr nvarchar (4000), - Table name, view name, query statement
@PageSize INT = 10, - Size per page (number of lines)
@PAGECURRENT INT = 1, - Page to display
@Fdshow nvarchar (4000) = '' ,- To display the list of fields, if the query result is identified by identity field, you need to specify this value without containing the identification field.
@Fdorder nvarchar (1000) = '- List of Sort Fields
AS
Declare @fdname nvarchar (250) - The primary key or table in the table, the identity column name in the temporary table
, @ Id1 varchar (20), @ id2 varchar (20) - start and end record number
, @ Obj_id int - Object ID
- Treatment of composite primary keys in the table
Declare @strfd nvarchar (2000) - Composite primary key list
, @ strjoin nvarchar (4000) - Connection field
, @ Strwhere Nvarchar (2000) - Query Conditions
SELECT @ obj_id = Object_id (@querystr)
, @ Fdshow = case isnull (@fdshow, '') when '' Ten '*' Else '
' @ Fdshow end
, @ Fdorder = case isnull (@fdorder, ') when' 'Then' 'Else' Order By
' @ Fdorder end
, @ Querystr = case when @obj_id is not null dam
' @ Querystr else' (
' @ Querystr ') a 'end
- If the first page is displayed, you can use TOP to complete
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 if there is a logo 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 is no identity column in the table, check 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 there is no primary key in the table, use a temporary table
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 if 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 the identity column or the primary key for a single field - * /
LbuseIdentity:
EXEC ('SELECT TOP
' @ Id1 @ fdshow ' from
' @ Querystr
'Where
' @ Fdname ' Not in (SELECT TOP '
@ ID2 '
' @ Fdname ' from
' @ Querystr @ fdorder
')' @ Fdorder
)
Return
/ * - Treatment method for composite primary keys in the table - * /
lbusepk:
EXEC ('SELECT
' @ Fdshow ' from (SELECT TOP ' @ Id1 a. * From
(SELECT TOP 100 percent * from
' @ Querystr @ fdorder ') a
Left Join (SELECT TOP)
' @ ID2 '
' @ strfd '
From
' @ Querystr @ fdorder ') b on
' @ Strjoin '
WHERE
' @ Strwhere ') a '
)
Return
/ * - Method for processing 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
)
