高效分页存储过程

USE [Shenyi]
GO
/****** 对象:  StoredProcedure [dbo].[UP_PageList]    脚本日期: 11/13/2008 17:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[UP_PageList]
(
    @tblName VARCHAR(255)='',    --表名
    @GetFields VARCHAR(1000)='*',   --需要返回的列
    @OrderFldName VARCHAR(255), --排序的字段名,需要建立聚合索引提高效率
    @fldName VARCHAR(255), --主键字段或其他建立索引字段
    @PageSize INT=10,    --页面尺寸
    @PageIndex INT=1, --页码
    @docount INT=0, --是否返回记录总数0不返回，1返回
    @OrderType BIT=1, --设置排序类型0升序，1降序
    @strWhere VARCHAR(max)='' --查询条件（不要加where）
)
AS
DECLARE @SqlMain VARCHAR(5000)--主SQL语句
DECLARE @Temp VARCHAR(100) --临时变量
DECLARE @Order VARCHAR(400) -- 排序类型

--------------------------------------
--如果DoReturnCount为就返回记录总数--
--------------------------------------
IF @docount = 1
    BEGIN
        SET @SqlMain = 'SELECT COUNT(1) AS Total FROM [' + @tblName + ']'
        IF @strWhere !=''
            SET @SqlMain = @SqlMain + ' WHERE ' + @strWhere
    END
-------------------------------------------------------------------------------------------------
ELSE
    BEGIN
-------------------------------------
--根据排序规则设置@Temp与@Order变量--
-------------------------------------
        IF @OrderType = 1
            BEGIN
                SET @Temp = '<(SELECT MIN'
                SET @Order = ' ORDER BY [' + @OrderFldName + '] DESC'    
            END
        ELSE
            BEGIN
                SET @Temp = '>(SELECT MAX'
                SET @Order =' ORDER BY [' + @OrderFldName + '] ASC'    
            END
-----------------------------------------------------------------------
        SET @SqlMain = 'SELECT TOP ' + STR(@PageSize) + ' ' + @GetFields + '
                        FROM [' + @tblName + ']'
------------------------------------------
--如果是获取第一页就执行以下代码提高效率--
------------------------------------------
        IF @PageIndex = 1
            BEGIN
                IF @strWhere != ''
                    SET @SqlMain = @SqlMain + ' WHERE ' + @strWhere

                SET @SqlMain = @SqlMain + @Order            
            END
-------------------------------------------------------------------------------------
        ELSE
---------------------------------------------------------------
--------------------------SQL语句主体--------------------------
---------------------------------------------------------------
            BEGIN
                SET @SqlMain = @SqlMain + ' WHERE [' + @OrderFldName + '] ' + @Temp + '
                ([' + @OrderFldName + ']) FROM ( SELECT TOP ' + STR((@PageIndex - 1) * @PageSize) + '
                [' + @OrderFldName + '] FROM [' + @tblName + ']'
                
                IF @strWhere != ''
                    SET @SqlMain = @SqlMain + ' WHERE ' + @strWhere + ' ' + @Order + ') AS TableTemp) AND ' + @strWhere + ' ' + @Order
                ELSE
                    SET @SqlMain = @SqlMain + @Order + ') AS TableTemp)' + @Order
            END
    END
EXEC(@SqlMain)

 

 

高效的分页存储过程