• 两个sql server 2000的通用分页存储过程

    两个sql server 2000的通用分页存储过程

       收藏到： 雅虎收藏夹 百度收藏 Google书签 Yahoo书签 新浪ViVi 搜狐网摘 365Key网摘 天极网摘 diglog 和讯网摘 POCO网摘 YouNote网摘 博拉网 天下图摘 reddit BlinkList blogmarks blogmarks

    发布时间：2007-6-3 18:28:43
    　

    第一个支持唯一主键，第二支持多主键，测试过，效率一般

    CREATE PROC P_viewPage
        /*
            no_mIss 分页存储过程 2007.2.20  QQ:34813284
            适用于单一主键或存在唯一值列的表或视图         
        */

        @TableName VARCHAR(200),     --表名
        @FieldList VARCHAR(2000),    --显示列名
        @PrimaryKey VARCHAR(100),    --单一主键或唯一值键
        @Where VARCHAR(1000),        --查询条件 不含'where'字符
        @Order VARCHAR(1000),        --排序 不含'order by'字符，如id asc,userid 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(@TableName,'') = '' OR ISNULL(@FieldList,'') = ''
            OR ISNULL(@PrimaryKey,'') = ''
            OR @SortType < 1 OR @SortType >3
            OR @RecorderCount < 0 OR @PageSize < 0 OR @PageIndex < 0
        BEGIN       
            RETURN
        END
     
        DECLARE @new_where1 VARCHAR(1000)
        DECLARE @new_where2 VARCHAR(1000)
        DECLARE @new_order 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 SET @new_order = ' ORDER BY ' + @PrimaryKey + ' ASC'
                IF @SortType = 2 SET @new_order = ' ORDER BY ' + @PrimaryKey + ' DESC'
            END
        ELSE
            BEGIN
                SET @new_order = ' ORDER BY ' + @Order
            END

        SET @SqlCount = 'SELECT @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/'
                        + CAST(@PageSize AS VARCHAR)+') FROM ' + @TableName + @new_where1
       
        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
            BEGIN
                SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
                           + @TableName + @new_where1 + @new_order
            END
        ELSE
            BEGIN
                IF @SortType = 1
                    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_order +' ) AS TMP) '+ @new_order
                    END
                IF @SortType = 2
                    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_order + ') AS TMP) '+ @new_order                              
                    END      
                IF @SortType = 3
                    BEGIN
                        IF CHARINDEX(',',@Order) = 0 BEGIN RETURN END
                        SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
                                   + @TableName + @new_where2 + @PrimaryKey + ' NOT IN (SELECT TOP '
                                   + STR(@PageSize*(@PageIndex-1)) + ' ' + @PrimaryKey
                                   + ' FROM ' + @TableName + @new_where1 + @new_order + ')'
                                   + @new_order
                    END
            END
        EXEC(@Sql)
    GO

    CREATE PROC P_public_ViewPage_per
        /*
            no_mIss 通用分页存储过程 2007.3.1  QQ:34813284
            适用于联合主键/单主键/存在能确定唯一行列/存在能确定唯一行的多列 (用英文,隔开)
            调用：
                第一页查询时返回总记录和总页数及第一页记录：
                EXECUTE P_public_ViewPage_per 'TableName','col1,col2,col3,col4','pk1,pk2,pk3',
                    'col5>0 and col7<9','pk1 asc,pk2 asc,pk3 asc',0,10,1,
                    @TotalCount OUTPUT,@TotalPageCount OUTPUT
                其它页调用，比如第89页(假设第一页查询时返回总记录为2000000)：
                EXECUTE P_public_ViewPage_per 'TableName','col1,col2,col3,col4','pk1,pk2,pk3',
                    'col5>0 and col7<9','pk1 asc,pk2 asc,pk3 asc',2000000,10,89,
                    @TotalCount OUTPUT,@TotalPageCount OUTPUT
        */

        @TableName VARCHAR(200),     --表名
        @FieldList VARCHAR(2000),    --显示列名
        @PrimaryKey VARCHAR(100),    --单一主键或唯一值键或联合主键列表(用英文,隔开)或能确定唯一行的多列列表(用英文,隔开)
        @Where VARCHAR(1000),        --查询条件 不含'where'字符
        @Order VARCHAR(1000),        --排序 不含'order by'字符,用英文,隔开 
        @RecorderCount INT,          --记录总数 0:会返回总记录
        @PageSize INT,               --每页输出的记录数
        @PageIndex INT,              --当前页数
        @TotalCount INT OUTPUT,      --返回记录总数
        @TotalPageCount INT OUTPUT   --返回总页数
    AS

        SET NOCOUNT ON
       
        SET @FieldList = REPLACE(@FieldList,' ','')
        IF @FieldList = '*'
            BEGIN SET @FieldList = 'A.*'END
        ELSE
            BEGIN
                SET @FieldList = 'A.' + REPLACE(@FieldList,',',',A.')
            END
       
        WHILE CHARINDEX(', ',@Order)>0
        BEGIN
            SET @Order = REPLACE(@Order,', ',',')
        END

        IF ISNULL(@TableName,'') = '' OR ISNULL(@PrimaryKey,'') = ''       
            OR @RecorderCount < 0 OR @PageSize < 0 OR @PageIndex < 0
        BEGIN       
            RETURN
        END
     
        DECLARE @new_where1 VARCHAR(1000)
        DECLARE @new_where2 VARCHAR(1000)
        DECLARE @new_where3 VARCHAR(1000)
        DECLARE @new_where4 VARCHAR(1000)
        DECLARE @new_order1 VARCHAR(1000)
        DECLARE @new_order2 VARCHAR(1000)
        DECLARE @Fields VARCHAR(1000)
        DECLARE @Sql VARCHAR(8000)
        DECLARE @SqlCount NVARCHAR(4000)

        SET @Fields = @PrimaryKey + ','
        SET @new_where2 = ''
        SET @new_where4 = ''


        IF ISNULL(@where,'') = ''
            BEGIN
                SET @new_where1 = ' '
                SET @new_where3 = ' WHERE '
            END
        ELSE
            BEGIN           
                SET @new_where1 = ' WHERE ' + @where + ' '
                SET @new_where3 = ' WHERE 1=1 '
                        + REPLACE(' AND ' + @where,' AND ',' AND A.')+ ' AND '
            END
       
        WHILE CHARINDEX(',',@Fields)>0
        BEGIN
            SET @new_where2 = @new_where2
                + 'A.' + LTRIM(LEFT(@Fields,CHARINDEX(',',@Fields)-1))
                + ' = B.' + LTRIM(LEFT(@Fields,CHARINDEX(',',@Fields)-1)) + ' AND '
            SET @new_where4 = @new_where4
                + 'B.' + LTRIM(LEFT(@Fields,CHARINDEX(',',@Fields)-1)) + ' IS NULL AND '
            SET @Fields = SUBSTRING(@Fields,CHARINDEX(',',@Fields)+1,LEN(@Fields))
        END
        SET @new_where2 = LEFT(@new_where2,LEN(@new_where2)-4)
        SET @new_where4 = LEFT(@new_where4,LEN(@new_where4)-4)

        IF ISNULL(@order,'') = ''
            BEGIN
                SET @new_order1 = ''
                SET @new_order2 = ''
            END
        ELSE
            BEGIN
                SET @new_order1 = ' ORDER BY ' + @Order
                SET @new_order2 = ' ORDER BY '
                        + RIGHT(REPLACE(',' + @Order,',',', A.' ),
                                LEN(REPLACE(',' + @Order,',',', A.' ))-1)
            END

        SET @SqlCount = 'SELECT @TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/'
                        + CAST(@PageSize AS VARCHAR)+') FROM ' + @TableName
                        + ' A ' +  @new_where1
       
        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
            BEGIN
                SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
                           + @TableName + ' A'+ @new_where1 + @new_order1
            END
        ELSE
            BEGIN
                SET @Sql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @FieldList + ' FROM '
                           + @TableName + ' A LEFT JOIN (SELECT TOP '
                           + STR(@PageSize*(@PageIndex-1))
                           + ' ' + @PrimaryKey + ' FROM ' + @TableName + @new_where1
                           + @new_order1 + ' )B ON ' + @new_where2 + @new_where3
                           + @new_where4 + @new_order2
            END

    EXEC(@Sql)
    GO
  • 相关阅读:
    linux 学习笔记1
    IIS请求筛选模块被配置为拒绝超过请求内容长度的请求
    ipod锁定后的恢复
    HTTP报文
    数据仓库概念
    数据挖掘概念
    大数据处理工具
    eclipse 4.3 汉化
    在CentOS中安装输入法
    编译Hadoop1.1.2eclipse插件并测试
  • 原文地址：https://www.cnblogs.com/flyfish/p/1256570.html
Copyright © 2020-2023  润新知