Heim > Artikel > Datenbank > mssql千万级分页存储过程分享

mssql千万级分页存储过程分享

WBOYOriginal: 2016-06-07 17:48:311021Durchsuche

文章找到了两篇关于mssql server存储过程的高效分页代码，有需要的朋友可以参考一下。

代码如下

复制代码

Create PROC P_viewPage
    /**//*
    nzperfect [no_mIss] 高效通用存储过程(双向检索) 2007.5.7 QQ:34813284
    敬告：适用于单一主键或存在唯一值列的表或视图
    :Sql语句为8000字节,调用时请注意传入参数及sql总长度不要超过指定范围
    */
    @TableName VARCHAR(200),     --表名
    @FieldList VARCHAR(2000),    --显示列名，如果是全部字段则为*
    @PrimaryKey VARCHAR(100),    --单一主键或唯一值键
    @Where VARCHAR(2000),        --查询条件不含'where'字符，如id>10 and len(userid)>9
    @Order VARCHAR(1000),        --排序不含'order by'字符，如id asc,userid desc，必须指定asc或desc
    --注意当@SortType=3时生效，记住一定要在最后加上主键，否则会让你比较郁闷
    @SortType INT,               --排序规则 1:正序asc 2:倒序desc 3:多列排序方法
    @RecorderCount INT,          --记录总数 0:会返回总记录
    @PageSize INT,               --每页输出的记录数
    @PageIndex INT,              --当前页数
    @TotalCount INT OUTPUT ,      --记返回总记录
    @TotalPageCount INT OUTPUT   --返回总页数
AS
SET NOCOUNT ON
    IF ISNULL(@TotalCount,'') = '' SET @TotalCount = 0
    SET @Order = RTRIM(LTRIM(@Order))
    SET @PrimaryKey = RTRIM(LTRIM(@PrimaryKey))
    SET @FieldList = REPLACE(RTRIM(LTRIM(@FieldList)),' ','')
    WHILE CHARINDEX(', ',@Order) > 0 or CHARINDEX(' ,',@Order) > 0
        BEGIN
            SET @Order = REPLACE(@Order,', ',',')
            SET @Order = REPLACE(@Order,' ,',',')
        END
    IF ISNULL(@TableName,'') = '' or ISNULL(@FieldList,'') = ''
            or ISNULL(@PrimaryKey,'') = ''
            or @SortType 3
            or @RecorderCount         BEGIN
            PRINT('ERR_00')
            RETURN
        END
    IF @SortType = 3
        BEGIN
            IF (UPPER(RIGHT(@Order,4))!=' ASC' AND UPPER(RIGHT(@Order,5))!=' DESC')
                BEGIN PRINT('ERR_02') RETURN END
        END
    DECLARE @new_where1 VARCHAR(1000)
    DECLARE @new_where2 VARCHAR(1000)
    DECLARE @new_order1 VARCHAR(1000)
    DECLARE @new_order2 VARCHAR(1000)
    DECLARE @new_order3 VARCHAR(1000)
    DECLARE @Sql VARCHAR(8000)
    DECLARE @SqlCount NVARCHAR(4000)
    IF ISNULL(@where,'') = ''
        BEGIN
            SET @new_where1 = ' '
            SET @new_where2 = ' Where '
        END
    ELSE
        BEGIN
            SET @new_where1 = ' Where ' + @where
            SET @new_where2 = ' Where ' + @where + ' AND '
        END
    IF ISNULL(@order,'') = '' or @SortType = 1 or @SortType = 2
        BEGIN
            IF @SortType = 1
                BEGIN
                    SET @new_order1 = ' orDER BY ' + @PrimaryKey + ' ASC'
                    SET @new_order2 = ' orDER BY ' + @PrimaryKey + ' DESC'
                END
            IF @SortType = 2
                BEGIN
                    SET @new_order1 = ' orDER BY ' + @PrimaryKey + ' DESC'
                    SET @new_order2 = ' orDER BY ' + @PrimaryKey + ' ASC'
                END
        END
    ELSE
        BEGIN
            SET @new_order1 = ' orDER BY ' + @Order
        END

    IF @SortType = 3 AND CHARINDEX(','+@PrimaryKey+' ',','+@Order)>0
    BEGIN
        SET @new_order1 = ' orDER BY ' + @Order
        SET @new_order2 = @Order + ','
        SET @new_order2 = REPLACE(REPLACE(@new_order2,'ASC,','{ASC},'),'DESC,','{DESC},')
        SET @new_order2 = REPLACE(REPLACE(@new_order2,'{ASC},','DESC,'),'{DESC},','ASC,')
        SET @new_order2 = ' orDER BY ' + SUBSTRING(@new_order2,1,LEN(@new_order2)-1)
        IF @FieldList '*'
            BEGIN
                SET @new_order3 = REPLACE(REPLACE(@Order + ',','ASC,',','),'DESC,',',')
                SET @FieldList = ',' + @FieldList
                WHILE CHARINDEX(',',@new_order3)>0
                    BEGIN
                        IF CHARINDEX(SUBSTRING(','+@new_order3,1,CHARINDEX(',',@new_order3)),','+@FieldList+',')>0
                            BEGIN
                                SET @FieldList =
                                @FieldList + ',' + SUBSTRING(@new_order3,1,CHARINDEX(',',@new_order3))
                            END
                        SET @new_order3 =
                        SUBSTRING(@new_order3,CHARINDEX(',',@new_order3)+1,LEN(@new_order3))
                    END
                SET @FieldList = SUBSTRING(@FieldList,2,LEN(@FieldList))
            END
        END

    SET @SqlCount = 'Select @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/'
    + CAST(@PageSize AS VARCHAR)+') FROM (Select * FROM ' + @TableName + @new_where1+') AS T'
    IF @RecorderCount = 0
        BEGIN
            EXEC SP_EXECUTESQL @SqlCount,N'@TotalCount INT OUTPUT,@TotalPageCount INT OUTPUT',
            @TotalCount OUTPUT,@TotalPageCount OUTPUT
        END
    ELSE
        BEGIN
            Select @TotalCount = @RecorderCount
        END
    IF @PageIndex > CEILING((@TotalCount+0.0)/@PageSize)
        BEGIN
            SET @PageIndex = CEILING((@TotalCount+0.0)/@PageSize)
        END
    IF @PageIndex = 1 or @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize)
        BEGIN
            IF @PageIndex = 1 --返回第一页数据
                BEGIN
                    SET @Sql = 'Select * FROM (Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
                    + @TableName + @new_where1 + @new_order1 +') AS TMP ' + @new_order1
                END
            IF @PageIndex >= CEILING((@TotalCount+0.0)/@PageSize) --返回最后一页数据
                BEGIN
                    SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('
                    + 'Select TOP ' + STR(ABS(@PageSize*@PageIndex-@TotalCount-@PageSize))
                    + ' ' + @FieldList + ' FROM '
                    + @TableName + @new_where1 + @new_order2 + ' ) AS TMP '
                    + @new_order1
                END
        END
    ELSE

        BEGIN
        IF @SortType = 1 --仅主键正序排序
            BEGIN
                IF @PageIndex                     BEGIN
                        SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
                        + @TableName + @new_where2 + @PrimaryKey + ' > '
                        + '(Select MAX(' + @PrimaryKey + ') FROM (Select TOP '
                        + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey
                        + ' FROM ' + @TableName
                        + @new_where1 + @new_order1 +' ) AS TMP) '+ @new_order1
                    END
                ELSE --反向检索
                    BEGIN
                        SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('
                        + 'Select TOP ' + STR(@PageSize) + ' '
                        + @FieldList + ' FROM '
                        + @TableName + @new_where2 + @PrimaryKey + '                         + '(Select MIN(' + @PrimaryKey + ') FROM (Select TOP '
                        + STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey
                        + ' FROM ' + @TableName
                        + @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2
                        + ' ) AS TMP ' + @new_order1
                    END
            END
        IF @SortType = 2 --仅主键反序排序
            BEGIN
                IF @PageIndex                     BEGIN
                        SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
                        + @TableName + @new_where2 + @PrimaryKey + '                         + '(Select MIN(' + @PrimaryKey + ') FROM (Select TOP '
                        + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey
                        +' FROM '+ @TableName
                        + @new_where1 + @new_order1 + ') AS TMP) '+ @new_order1
                    END
                ELSE --反向检索
                    BEGIN
                        SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ('
                        + 'Select TOP ' + STR(@PageSize) + ' '
                        + @FieldList + ' FROM '
                        + @TableName + @new_where2 + @PrimaryKey + ' > '
                        + '(Select MAX(' + @PrimaryKey + ') FROM (Select TOP '
                        + STR(@TotalCount-@PageSize*@PageIndex) + ' ' + @PrimaryKey
                        + ' FROM ' + @TableName
                        + @new_where1 + @new_order2 +' ) AS TMP) '+ @new_order2
                        + ' ) AS TMP ' + @new_order1
                    END
            END
        IF @SortType = 3 --多列排序，必须包含主键，且放置最后，否则不处理
            BEGIN
                IF CHARINDEX(',' + @PrimaryKey + ' ',',' + @Order) = 0
                    BEGIN PRINT('ERR_02') RETURN END
                    IF @PageIndex                         BEGIN
                            SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
                            + 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
                            + ' Select TOP ' + STR(@PageSize*@PageIndex) + ' ' + @FieldList
                            + ' FROM ' + @TableName + @new_where1 + @new_order1 + ' ) AS TMP '
                            + @new_order2 + ' ) AS TMP ' + @new_order1
                        END
                    ELSE --反向检索
                        BEGIN
                            SET @Sql = 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
                            + 'Select TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM ( '
                            + ' Select TOP ' + STR(@TotalCount-@PageSize *@PageIndex+@PageSize) + ' ' + @FieldList
                            + ' FROM ' + @TableName + @new_where1 + @new_order2 + ' ) AS TMP '
                            + @new_order1 + ' ) AS TMP ' + @new_order1
                        END
            END
        END
    PRINT(@SQL)
    EXEC(@Sql)

自己写的一个

代码如下

复制代码

USE [CaiLi]
GO

/****** Object: StoredProcedure [dbo].[SqlPagination] Script Date: 10/26/2011 11:40:46 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SqlPagination]

***************************************************************

** 千万数量级分页存储过程 **

***************************************************************

参数说明:

1.Tables :表名称,视图

2.PrimaryKey :主关键字

3.Sort :排序语句，不带Order By 比如：NewsID Desc,OrderRows Asc

4.CurrentPage :当前页码

5.PageSize :分页尺寸

6.Filter :过滤语句，不带Where

7.Group :Group语句,不带Group By

***************************************************************/

(

@Tables varchar(1000),

@PrimaryKey varchar(100),

@Sort varchar(200) = NULL,

@CurrentPage int = 1,

@PageSize int = 10,

@Fields varchar(1000) = '*',

@Filter varchar(1000) = NULL,

@Group varchar(1000) = NULL
)

/*默认排序*/
if @PrimaryKey IS NULL or @PrimaryKey = ''
set @PrimaryKey='ID'

IF @Sort IS NULL or @Sort = ''

SET @Sort = @PrimaryKey

IF @Fields IS NULL or @Fields = ''

SET @Fields = '*'

DECLARE @SortTable varchar(100)

DECLARE @SortName varchar(100)

DECLARE @strSortColumn varchar(200)

DECLARE @operator char(2)

DECLARE @type varchar(100)

DECLARE @prec int

/*设定排序语句.*/
if charindex(',',@Sort) >0
set @strSortColumn = substring(@Sort,0,charindex(',',@Sort))
else
set @strSortColumn = @Sort
IF CHARINDEX('DESC',@Sort)>0

BEGIN

SET @strSortColumn = REPLACE(@strSortColumn, 'DESC', '')

SET @operator = '

END

ELSE

BEGIN

IF CHARINDEX('ASC',@Sort)> 0
BEGIN
SET @strSortColumn = REPLACE(@strSortColumn, 'ASC', '')

SET @operator = '>='
END
END

IF CHARINDEX('.', @strSortColumn) > 0

BEGIN

SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))

SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))

END

ELSE

BEGIN

SET @SortTable = @Tables

SET @SortName = @strSortColumn

END

Select @type=t.name, @prec=c.prec

FROM sysobjects o

JOIN syscolumns c on o.id=c.id

JOIN systypes t on c.xusertype=t.xusertype

Where o.name = @SortTable AND c.name = @SortName

IF CHARINDEX('char', @type) > 0

SET @type = @type + '(' + CAST(@prec AS varchar) + ')'

DECLARE @strPageSize varchar(50)

DECLARE @strStartRow varchar(50)

DECLARE @strFilter varchar(1000)

DECLARE @strSimpleFilter varchar(1000)

DECLARE @strGroup varchar(1000)

DECLARE @strSort varchar(200)

/*默认当前页*/

IF @CurrentPage

SET @CurrentPage = 1

/*设置分页参数.*/

SET @strPageSize = CAST(@PageSize AS varchar(50))

SET @strStartRow = CAST(((@CurrentPage - 1)*@PageSize + 1) AS varchar(50))

/*筛选以及分组语句.*/

IF @Filter IS NOT NULL AND @Filter != ''

BEGIN

SET @strFilter = ' Where 1=1 ' + @Filter + ' '

SET @strSimpleFilter =@Filter + ' '

END

ELSE

BEGIN

SET @strSimpleFilter = ''

SET @strFilter = ''

END

IF @Group IS NOT NULL AND @Group != ''

SET @strGroup = ' GROUP BY ' + @Group + ' '

ELSE

SET @strGroup = ''

IF @Sort IS NOT NULL AND @Sort != ''

SET @strSort = ' ORDER BY ' + @Sort + ' '

ELSE

SET @strSort = ''

--print('Select ' + @Fields + ' FROM ' + '(Select *,ROW_NUMBER() OVER ('+@strSort+')as RowNumber FROM '+@Tables+') t' + ' Where t.RowNumber between '+@strStartRow+' and '+' ' + @strSimpleFilter + ' ' + @strSort + @strGroup)
/*执行查询语句*/
declare @STRORDER varchar(50)
if CHARINDEX(',',@strSort)>0
set @STRORDER=SUBSTRING(@strSort, 0, CHARINDEX(',',@strSort))
else
set @STRORDER=@strSort
EXEC(

' DECLARE @SortColumn ' + @type + '
DECLARE @TotalCount int
DECLARE @ENDCOUNT int
DECLARE @strENDCOUNT varchar(50)

--Select count(1) FROM ' + @Tables + @strFilter+'

set @TotalCount=(Select count(1) FROM ' + @Tables + @strFilter+')'+'
SET ROWCOUNT ' + @strStartRow + '
SET @ENDCOUNT=CAST('+@strStartRow+' AS int)+CAST('+@strPageSize+' AS int)-1

IF @ENDCOUNT > @TotalCount
BEGIN
SET @ENDCOUNT = @TotalCount
END
set @strENDCOUNT=CAST(@ENDCOUNT AS varchar(50))

Select @SortColumn=' + @strSortColumn + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + @strSort + '

SET ROWCOUNT ' + @strPageSize + '

Select ' + @Fields + ' FROM ' + '(Select *,ROW_NUMBER() OVER ('+@STRORDER+')as RowNumber FROM '+@Tables+' where 1=1 '+@strSimpleFilter+') t' + ' Where t.RowNumber between '+@strStartRow+' and @strENDCOUNT ' + @strGroup + @strSort + ' ')
GO

Stellungnahme：

Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn

Vorheriger Artikel：仿百度的sql分页样式分享Nächster Artikel：mssql server创建表与保存数据语句

In Verbindung stehende Artikel

Mehr sehen