Recently found a good SQL stored procedure, I feel very good, the execution efficiency can also be recommended, I recommend it to everyone.
-
Create
PROC
UP_GETTOPICLIST
@A_TABLIST
VARCHAR
(
200
), The field to be queried
@A_TableName
VARCHAR
(
30
), The table name to query
@a_selectwhere
VARCHAR
(
500
), - Query restrictions
@a_selectorderIDERID
VARCHAR
(
20
), - Query the primary key
@A_SelectOrder
VARCHAR
(
50
), Sort field
@A_INTPAGENO
int
,
@A_INTPAGESIZE
int
,
@Recordcount
int
OUTPUT
AS
/ ** /
/ * Define local variables * /
Declare
@intbeginid
int
Declare
@Intendid
int
Declare
@introotrecordcount
int
Declare
@INTROWCOUNT
int
Declare
@Tmpselect
Nvarchar
(
600
)
/ ** /
/ * Close count * /
set
Nocount
on
/ ** /
/ * Ask for a total of total roots * /
SELECT
@Tmpselect
=
'
Set nocount on; select @spintrootrecordcount = count (*) from
'
@A_TableName
'
'
@a_selectwhere
EXECUTE
SP_EXECUTESQL
@Tmpselect
N
'
@Spintrootrecordcount Int OUTPUT
'
,
@Spintrootrecordcount
=
@introotrecordcount
OUTPUT
SELECT
@Recordcount
=
@introotrecordcount
IF
(
@introotrecordcount
=
0
)
-
Return zero if there is no post
Return
0
/ ** /
/ * Is judging if the number of pages is correct * /
IF
(
@A_INTPAGENO
-
1
)
*
@A_INTPAGESIZE
>
@introotrecordcount
Return
(
-
1
)
/ ** /
/ * Seeking rootid * /
set
@INTROWCOUNT
=
(
@A_INTPAGENO
-
1
)
*
@A_INTPAGESIZE
1
/ ** /
/ * Restriction bar * /
SELECT
@Tmpselect
=
'
Set nocount on; set rowcount @spintrowcount; select @spintbeginid =
'
@a_selectorderIDERID
'
From
'
@A_TableName
'
'
@a_selectwhere
'
'
@A_SelectOrder
EXECUTE
sp_executesql @ TmpSelect
N
'
@SpintrowCount Int, @ spintbeginid int output
'
,
@Spintrowcount
=
@INTROWCOUNT
,
@Spintbeginid
=
@intbeginid
OUTPUT
/ ** /
/ * End rootid * /
set
@INTROWCOUNT
=
@A_INTPAGENO
*
@A_INTPAGESIZE
/ ** /
/ * Restriction bar * /
SELECT
@Tmpselect
=
'
Set nocount on; set rowcount @spintrowcount; select @spintendID =
'
@a_selectorderIDERID
'
From
'
@A_TableName
'
'
@a_selectwhere
'
'
@A_SelectOrder
EXECUTE
SP_EXECUTESQL
@Tmpselect
N
'
@Spintrowcount int, @ spintendid Int Output
'
,
@Spintrowcount
=
@INTROWCOUNT
,
@SpintendidID
=
@Intendid
OUTPUT
IF
@a_selectwhere
=
'' '
oral
@a_selectwhere
IS
NULL
SELECT
@Tmpselect
=
'
Set NoCount Off; Set Rowcount 0; SELECT
'
@A_TABLIST
'
From
'
@A_TableName
'
WHERE
'
@a_selectorderIDERID
'
Between
'
Else
SELECT
@Tmpselect
=
'
Set NoCount Off; Set Rowcount 0; SELECT
'
@A_TABLIST
'
From
'
@A_TableName
'
'
@a_selectwhere
'
and
'
@a_selectorderIDERID
'
Between
'
IF
@Intendid
>
@intbeginid
SELECT
@Tmpselect
=
@Tmpselect
'
@SPintBeginid and @spintendID
'
'
'
@A_SelectOrder
Else
SELECT
@Tmpselect
=
@Tmpselect
'
@Spintendid and @spintbeginid
'
'
'
@A_SelectOrder
EXECUTE
SP_EXECUTESQL
@Tmpselect
N
'
@Spintendid Int, @ spintbeginid INT
'
,
@SpintendidID
=
@Intendid
,
@Spintbeginid
=
@intbeginid
Return
(
@@ rowcount
)
-
SELECT @@ rowcount
Go
