IF
Object_id
(N
'
DBO.P_SHOW
'
)
IS
NOT
NULL
Drop
Procedure
DBO.P_SHOW
Go
/ ** /
/ * - Implement the universal stored procedure of the pagination display Specify table, view, query results for the primary key or identity column in the table, directly from the original table, other cases, if views or query results have a primary key, this method is not recommended if you are using the query, and the query using the order by, the query must contain a statement to top last updated: 2008.01.20-- Zou 2003.09 (quoted, please retain this information) - - * /
/ ** /
/ * - Call example exec dbo.P_show @querystr = n'tb ', @pageSize = 5, @pagecurrent = 3, @fdshow =' id, colid, name ', @fdorder =' colid, name'select ID, colid from tborder by colid, nameEXEC dbo.p_show @QueryStr = N'SELECT TOP 100 PERCENT * fROM dbo.sysobjectsORDER bY xtype ', @PageSize = 5, @PageCurrent = 2, @FdShow =' name, xtype ', @FdOrder = 'xipe, name' - * /
Create
PROC
DBO.P_SHOW
@Querystr
nvarchar
(
4000
),
-
Table name, view name, query statement
@PageSize
int
=
10
,
-
Size of each page (number of lines)
@PAGECURRENT
int
=
1
,
-
Page to display
@Fdshow
nvarchar
(
4000
)
=
N
'' '
,
-
To display the list of fields, if the query result does not require the identity field, you need to specify this value and do not include the identification field.
@Fdorder
nvarchar
(
1000
)
=
N
'' '
-
Sort field list
AS
Set
Nocount
On
Declare
@FDNAME
Sysname,
-
The primary key or table in the table, the identity column name in the temporary table
@ Id1
Sysname,
-
Start and end record number
@ Id2
Sysname,
@Obj_id
int
-
Object ID
-
Table of composite primary key 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
WHEN
@Fdshow
>
N
'' '
THEN
N
'
'
@Fdshow
Else
N
'
*
'
End
,
@Fdorder
=
Case
WHEN
@Fdorder
>
N
'' '
THEN
N
'
ORDER BY
'
@Fdorder
Else
N
'
'
End
,
@Querystr
=
Case
WHEN
@Obj_id
IS
NULL
THEN
N
'
(
'
@Querystr
N
'
) A
'
Else
N
'
'
@Querystr
End
-
If you display the first page, you can use top to complete
IF
@PAGECURRENT
=
1
Begin
SELECT
@ Id1
=
CAST
(
@PageSize
AS
VARCHAR
(
20
))
EXEC
(N
'
SELECT TOP
'
@ Id1
N
'
'
@Fdshow
N
'
From
'
@Querystr
N
'
'
@Fdorder
)
Return
End
-
If it is a table, check if there is a logo or primary key in the table.
IF
@Obj_id
IS
NULL
Oral
ObjectProperty
(
@Obj_id
,
'
IStable
'
)
=
0
Goto
LB_USETEMP
Else
Begin
SELECT
@ Id1
=
CAST
(
@PageSize
AS
VARCHAR
(
20
))
@ Id2
=
CAST
(("
@PAGECURRENT
-
1
)
*
@PageSize
AS
VARCHAR
(
20
))
-
Label
SELECT
@FDNAME
=
Name
From
DBO.SYSCOLUMNS
WHERE
id
=
@Obj_id
AND
STATUS
=
0x80
IF
@@ rowcount
=
0
-
If there is no marking column in the table, check if there is a primary key in the table.
Begin
Declare
@pk_number
int
SELECT
@strfd
=
N
'' '
,
@Strjoin
=
N
'' '
,
@Strwhere
=
N
'' '
SELECT
@strfd
=
@strfd
N
'
,
'
Quotename
(Name),
@Strjoin
=
@Strjoin
N
'
And A.
'
Quotename
(Name)
N
'
= B.
'
Quotename
(Name),
@Strwhere
=
@Strwhere
N
'
And B.
'
Quotename
(Name)
N
'
Is NULL
'
From
(
SELECT
Ix.id, ix.indid, ixc.colid, ixc.keyno, c.namefrom
DBO.SYSObjects O, Dbo.sysIndexes ix, dbo.sysindexkeys IXC, DBO.SYSCOLUMNS C
WHERE
O.Parent_obj
=
@Obj_id
AND
O.XTYPE
=
'
PK
'
AND
O.Name
=
Ix.name
AND
Ix.id
=
@Obj_id
AND
Ix.id
=
IXc.id
AND
Ix.indid
=
Ixc.indid
AND
IXc.id
=
C.ID
AND
Ixc.colid
=
C.colid) a
ORDER
BY
Keyno
SELECT
@pk_number
=
@@ rowcount
,
@strfd
=
Stuff
(
@strfd
,
1
,
1
N
'' '
),
@Strjoin
=
Stuff
(
@Strjoin
,
1
,
5
N
'' '
),
@Strwhere
=
Stuff
(
@Strwhere
,
1
,
5
N
'' '
)
IF
@pk_number
=
0
Goto
LB_USETEMP
-
If there is no primary key in the table, use a temporary table
Else
IF
@pk_number
=
1
Begin
SELECT
@FDNAME
=
@strfd
Goto
LB_USEIDENTITY
-
Use a single main key
End
Else
Goto
LB_USEPK
-
Use the composite primary key
End
End
/ ** /
/ * - Use the identity column or the primary key for a single field - * /
LB_USEIDENTITY:
EXEC
(N
'
SELECT TOP
'
@ Id1
N
'
'
@Fdshow
N
'
From
'
@Querystr
N
'
WHERE
'
@FDNAME
'
Not in (SELECT TOP
'
@ Id2
N
'
'
@FDNAME
'
From
'
@Querystr
N
'
'
@Fdorder
N
'
)
'
@Fdorder
N
'
'
)
Return
/ ** /
/ * - Treatment method for composite primary keys in the table - * /
LB_USEPK:
EXEC
(N
'
SELECT
'
@Fdshow
N
'
From (SELECT TOP)
'
@ Id1
N
'
A.1 * from
'
@ Querystr
N
'
A left join
'
@ Id2
N
'
'
@strfd
N
'
From
'
@Querystr
N
'
'
@Fdorder
N
'
) B on
'
@Strjoin
N
'
WHERE
'
@Strwhere
N
'
'
@Fdorder
N
'
) A
'
@Fdorder
N
'
'
)
Return
/ ** /
/ * - Method for processing temporary table - * /
LB_USETEMP:
SELECT
@FDNAME
=
Quotename
(N
'
ID_
'
CAST
(
NewID
()
AS
VARCHAR
(
40
)))
@ Id1
=
CAST
(
@PageSize
*
(
@PAGECURRENT
-
1
)
AS
VARCHAR
(
20
))
@ Id2
=
CAST
(
@PageSize
*
@PAGECURRENT
-
1
AS
VARCHAR
(
20
))
EXEC
(N
'
SELECT
'
@FDNAME
N
'
= Identity (int, 0, 1),
'
@Fdshow
N
'
INTO #TBFROM (SELECT TOP 100 Percent * from
'
@Querystr
N
'
'
@Fdorder
N
'
) A
'
@Fdorder
N
'
SELECT
'
@Fdshow
N
'
From #tb where
'
@FDNAME
'
Between
'
@ Id1
'
AND
'
@ Id2
N
'
'
)
Go
