|
?
/**/
/*=======================================================================??
功能:?对传进来的查询SQL进行分页后返回指定页数据,?不管查询多少页,其查询性能只受?@SqlStr?自身性能的影响,有时还要快
参数:?@SqlStr?需要执行的查询语句
??????@PrimaryKey?参数@SqlStr返回列中具有唯一确定性的字段名,不能有表别名前缀
??????@OrdrStr?参数@SqlStr返回列中存的字段,用于排序,不能有表别名前缀
??????@CurrPageNo?需要返回的页数
??????@PageSize?每页行数
??????@TotalNum?当传进来为?-1?时,返回符合条件的总行数
author:?杨连山
date:?2008-01-25?
测试代码:
SELECT?GETDATE()
DECLARE?
??@SqlStr?????VARCHAR(8000),
??@CurrPageNo?INT,
??@PageSize???INT,
??@TotalNum???INT
SET?@SqlStr?=?
'SELECT?*
FROM?PROD_PRODUCT?WITH(NOLOCK)
WHERE?CONTAINS(ProductName,?''手机'')'
SET?@CurrPageNo?=?700
SET?@PageSize?=?20
SET?@TotalNum?=?-1
EXEC?COMMON_PROCEDURE_QueryWithPage?@SqlStr,?'ProductPKId',?'UpdateDateTime',?@CurrPageNo,?20,?@TotalNum?output
SELECT?@TotalNum,?GETDATE()
=========================================================================*/
??
IF
?
EXISTS
(
SELECT
?
*
?
FROM
?sysobjects?
WHERE
?name?
=
?
'
COMMON_PROCEDURE_QueryWithPage
'
?
AND
?type?
=
?
'
P
'
)
??
DROP
?
PROC
?COMMON_PROCEDURE_QueryWithPage
GO
CREATE
?
PROC
?COMMON_PROCEDURE_QueryWithPage
(
????
@SqlStr
????????
VARCHAR
(
5000
),
????
@PrimaryKey
????
VARCHAR
(
100
),
????
@OrderStr
??????
VARCHAR
(
500
),
????
@CurrPageNo
????
INT
,
????
@PageSize
??????
INT
,
????
@TotalNum
??????
INT
?OUTPUT
)
AS
DECLARE
??
@ExeSql
?
VARCHAR
(
8000
)
SET
?NOCOUNT?
ON
IF
?
@TotalNum
?
=
?
-
1
??
CREATE
?
TABLE
?#Table_Count(TotalNum?
INT
)
SET
?
@ExeSql
?
=
?
'
SELECT?IDENTITY(INT)?AS?RowIndex,?A.
'
?
+
?
@PrimaryKey
?
+
?
CHAR
(
10
)?
+
'
INTO?#Table_Data
FROM?(
'
?
+
?
@SqlStr
?
+
?
'
)?AS?A
'
IF
?
@OrderStr
?
<>
?
''
BEGIN
??
SET
?
@ExeSql
?
=
?
@ExeSql
?
+
?
CHAR
(
10
)?
+
????
'
ORDER?BY?
'
?
+
?
@OrderStr
END
IF
?
@TotalNum
?
=
?
-
1
BEGIN
??
SET
?
@ExeSql
?
=
?
@ExeSql
?
+
?
CHAR
(
10
)?
+
?
CHAR
(
10
)?
+
????
'
INSERT?INTO?#Table_Count?VALUES(ISNULL(SCOPE_IDENTITY(),?0))
'
END
SET
?
@ExeSql
?
=
?
@ExeSql
?
+
?
CHAR
(
10
)?
+
?
CHAR
(
10
)?
+
'
SELECT?*?
FROM?(
'
?
+
?
@SqlStr
?
+
?
'
)?AS?A
WHERE?A.
'
?
+
?
@PrimaryKey
?
+
?
'
?IN(SELECT?
'
?
+
?
@PrimaryKey
?
+
?
'
?FROM?#Table_Data?WHERE?RowIndex?BETWEEN?
'
?
+
?
??
CONVERT
(
VARCHAR
,?(
@CurrPageNo
-
1
)
*
@PageSize
+
1
)?
+
?
'
?AND?
'
?
+
?
CONVERT
(
VARCHAR
,?
@CurrPageNo
*
@PageSize
)?
+
?
'
)
'
IF
?
@OrderStr
?
<>
?
''
BEGIN
??
SET
?
@ExeSql
?
=
?
@ExeSql
?
+
?
CHAR
(
10
)?
+
????
'
ORDER?BY?
'
?
+
?
@OrderStr
END
EXEC
(
@ExeSql
)
IF
?
@TotalNum
?
=
?
-
1
BEGIN
??
SELECT
?
@TotalNum
?
=
?Totalnum?
FROM
?#Table_Count
??
DROP
?
TABLE
?#Table_Count
??
IF
?
@TotalNum
?
IS
?
NULL
????
SET
?
@TotalNum
?
=
?
-
1
END
RETURN
GO
?
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
