/**/
/*=======================================================================
功能: 对传进来的查询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
功能: 对传进来的查询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