文档章节

MSSQL分页存储过程

dong_zq
 dong_zq
发布于 2016/07/16 14:52
字数 1395
阅读 3
收藏 0
点赞 0
评论 0

本文转自http://www.oschina.net/code/snippet_933757_33542借鉴学习

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
README
Version 1.0.5
本存储过程是本人查找对比网络上常用的分页存储过程后改写的分页存储过程,拥有较高效率,但不保证效率最高。
本存储过程是在MSSQL2012上编写,不保证兼容所有版本MSSQL(已知MSSQL2005需要修改少量代码)。不兼容其他数据库。
本分页存储过程仅支持常用SQL语句。
若发现问题,请联系ttio4116@live.com,共同进步!!
 
版本更新:
Version 1.0.1:修复了查询第一页数据时会采用错误的SQL语句的BUG
Version 1.0.2:更新了部分参数的解释
Version 1.0.3:修复了多表查询时由于表名错误导致的出错
Version 1.0.4:去除了部分不需要的条件判断语句
Version 1.0.5:修复了第一页时GROUP BY的错误,现在可以在@FldName里面加入COUNT()、MAX()等(不限于此)聚合函数
 
注意:1.HAVING语句需要与GROUP BY语句配合使用,格式为:GROUP BY XXX HAVING XXX。
      2.@FldSort需要给需要排序的字段设置ASC或者DESC。
      3.@strOrder为排序&聚合参数,排序时在没有设置@FldSort时起作用(默认正序排列);聚合时为了计算总条数,若该参数为空时则取@FldSort的第一个字段。
      4.建议@strOrder设置为主键,就算不是主键也不要包含NULL,否则会发生不可预料的结果。若该参数为空,请务必使@FldSort的第一个字段不含NULL。
*/
CREATE PROCEDURE [dbo].[MyPageRead]
(
@TblName nvarchar(3000)     --连接的表名,即FROM后面的内容
,@FldName nvarchar(3000)='*'     --要查询的字段名称,默认为全部
,@FldSort nvarchar(3000)=NULL     --排序字段,不需要ORDER BY,排序自行设置,请加入ASC或者DESC
,@strCondition nvarchar(3000)=NULL     --要查询的语句,不需要WHERE,前面不需要跟AND或者OR,但是不会影响计算
,@strGroup nvarchar(3000)=NULL     --要聚合的语句,不需要GROUP BY
,@strHaving nvarchar(3000)=NULL     --HAVING语句,不需要HAVING
,@Dist bit=0     --是否去除重复数据,0不去除/1去除
,@strOrder nvarchar(1000)=NULL     --一个排序字段,当@FldSort为空时必须指定。而且该字段用于计算总条数,该字段为空时选取@FldSort的第一个字段
,@OnlyCounts bit=0     --是否只返回总条数而不进行分页
 
,@PageSize int=10     --每页要显示的数量
,@Page int=1     --要显示那一页的数据
 
,@Counts int=1 output     --返回总条数
,@PageCounts int=1 output     --返回总页数
)
AS
SET NOCOUNT ON     --不返回计数
--定义变量
DECLARE @tmpFldSort nvarchar(3000)     --构成的ORDER BY语句存放处
DECLARE @tmpstrCondition nvarchar(3000)     --WHERE语句存放处
DECLARE @tmpstrGroup nvarchar(3000)     --GROUP BY语句存放处
DECLARE @tmpstrfirst nvarchar(3000)     --1.开头存放处,控制Dist
DECLARE @tmpstrfirstCount nvarchar(3000)     --2.开头存放处,控制Dist
 
/*计算时间*/
DECLARE @StartTime datetime
SET @StartTime=GETDATE()
 
 
IF (@FldSort IS NULL OR @FldSort='') AND (@strOrder IS NULL OR @strOrder='')
    RETURN
--必须有一个有值,如果有问题,直接跳出
 
IF @FldSort IS NULL OR @FldSort=''
    SET @tmpFldSort=@strOrder+' ASC '
ELSE
    SET @tmpFldSort=@FldSort
--以上为设置ORDER BY语句
 
IF @strCondition IS NULL OR @strCondition=''
    SET @tmpstrCondition=''
ELSE
    BEGIN
        IF CHARINDEX('AND ',LTRIM(@strCondition))=1
            SET @strCondition=RIGHT(@strCondition,LEN(@strCondition)-4)
        IF CHARINDEX('OR ',LTRIM(@strCondition))=1
            SET @strCondition=RIGHT(@strCondition,LEN(@strCondition)-3)
        SET @tmpstrCondition=' WHERE '+@strCondition
    END
--以上为设置WHERE语句
 
IF @strGroup IS NULL OR @strGroup=''
    SET @tmpstrGroup=''
ELSE
    BEGIN
        SET @tmpstrGroup=' GROUP BY '+@strGroup
        IF @strHaving IS NOT NULL AND @strHaving<>''
            SET @tmpstrGroup=@tmpstrGroup+' HAVING '+@strHaving
    END
--以上为设置GROUP BY语句
 
DECLARE @tmpFldsubstr nvarchar(1000)     --排序的第一个字段,用于计算总数据量,当@strOrder无数据时有效
IF @Dist=0
    BEGIN
        SET @tmpstrfirst=' SELECT '
        IF @strOrder IS NULL OR @strOrder=''
            BEGIN
                SET @tmpFldsubstr=LEFT(LTRIM(@FldSort),CHARINDEX(CHAR(32),LTRIM(@FldSort)))
                SET @tmpstrfirstCount=' SELECT @Counts=COUNT('+@tmpFldsubstr+')'
            END
        ELSE
            SET @tmpstrfirstCount=' SELECT @Counts=COUNT('+@strOrder+')'
    END
ELSE
    BEGIN
        SET @tmpstrfirst=' SELECT DISTINCT '
        IF @strOrder IS NULL OR @strOrder=''
            BEGIN
                SET @tmpFldsubstr=LEFT(LTRIM(@FldSort),CHARINDEX(CHAR(32),LTRIM(@FldSort)))
                SET @tmpstrfirstCount=' SELECT @Counts=COUNT(DISTINCT '+@tmpFldsubstr+')'
            END
        ELSE
            SET @tmpstrfirstCount=' SELECT @Counts=COUNT(DISTINCT '+@strOrder+')'
    END
--以上通过@Dist设置@Counts
 
DECLARE @sqlStr nvarchar(3000)     --查询的sql语句
IF @tmpstrGroup=''
    SET @sqlStr=@tmpstrfirstCount+' FROM '+@TblName+@tmpstrCondition
ELSE
    BEGIN
        SET @tmpstrfirstCount=REPLACE(@tmpstrfirstCount,'@Counts=','')
        SET @sqlStr='SELECT @Counts=COUNT(*) FROM ('+@tmpstrfirstCount+'AS tmpF FROM '+@TblName+@tmpstrCondition+@tmpstrGroup+') AS tmpT'
    END
EXEC sp_executesql @sqlStr,N'@Counts int out ',@Counts out     --返回查找到的总数
 
IF @OnlyCounts=1
    RETURN
--如果@OnlyCounts=1,则直接返回总条数
 
DECLARE @tmpCounts int
IF @Counts=0
    SET @tmpCounts=1 
ELSE
    SET @tmpCounts=@Counts
 
SET @PageCounts=(@tmpCounts+@PageSize-1)/@PageSize
--以上获得分页总数
 
IF @Page<1
    SET @Page=1
IF @Page>@PageCounts
    SET @Page=@PageCounts
--以上设置分页
 
DECLARE @tmpsql nvarchar(3000)     --设置最后要查询的SQL语句
 
IF @Page=1     --当取第一页时,用最快的算法
    SET @tmpsql=@tmpstrfirst+' TOP '+CAST(@PageSize AS nvarchar(50))+' '+@FldName+' FROM '+@TblName+@tmpstrCondition+@tmpstrGroup+' ORDER BY '+@tmpFldSort
IF @Page>1 AND @Page<=@PageCounts/2     --这是要查询的页在总分页数的前半
    BEGIN
        SET @tmpsql='WITH temptbl AS(SELECT TOP '+CAST(@Page*@PageSize AS nvarchar(50))+' ROW_NUMBER() OVER(ORDER BY '+@tmpFldSort+') AS tmpRowIndex,'+@FldName+' FROM '+@TblName+' '+@tmpstrCondition+' '+@tmpstrGroup+') '
        SET @tmpsql=@tmpsql+'SELECT * FROM temptbl WHERE [tmpRowIndex] BETWEEN '+CAST((@Page-1)*@PageSize+1 AS nvarchar(50))+' AND '+CAST((@Page-1)*@PageSize+@PageSize AS nvarchar(50))
    END
IF @Page>1 AND @Page>@PageCounts/2     --从后面查在总分页数的后半数据
    BEGIN
        SET @tmpFldSort=REPLACE(@tmpFldSort,' ASC',' [~1]')
        SET @tmpFldSort=REPLACE(@tmpFldSort,' DESC',' [~2]')
        SET @tmpFldSort=REPLACE(@tmpFldSort,'[~1]','DESC')
        SET @tmpFldSort=REPLACE(@tmpFldSort,'[~2]','ASC')
        --优化后半部分数据查询,把查询条件互换,[~1]为DESC,[~2]为ASC
        SET @tmpsql='WITH temptbl AS(SELECT TOP '+CAST(@Counts-(@Page-1)*@PageSize AS nvarchar(50))+' ROW_NUMBER() OVER(ORDER BY '+@tmpFldSort+') AS tmpRowIndex,'+@FldName+' FROM '+@TblName+' '+@tmpstrCondition+' '+@tmpstrGroup+') '
        SET @tmpsql=@tmpsql+'SELECT * FROM temptbl WHERE [tmpRowIndex] BETWEEN '+CAST(@Counts-((@Page-1)*@PageSize+@PageSize-1) AS nvarchar(50))+' AND '+CAST(@Counts-((@Page-1)*@PageSize) AS nvarchar(50))+' ORDER BY tmpRowIndex DESC'
    END
 
--SELECT @tmpsql     查看拼接的字符串
EXEC sp_executesql @tmpsql
 
/*计算时间*/
--SELECT DATEDIFF(MS,@StartTime,GETDATE()) AS [Time]
/**/
SET NOCOUNT OFF

 

本文转载自:http://www.oschina.net/code/snippet_933757_33542

共有 人打赏支持
dong_zq
粉丝 0
博文 10
码字总数 5386
作品 0
郑州
高级程序员
FreeBSD下执行SQL Server2005存储过程解决方案

FreeBSD下执行SQL Server2005存储过程解决方案(Warning: mssql_execute(): ...) FreeBSD下使用SQL Server2005(2008)需要使用FreeTDS。 安装FreeTSD后,使用PHP语言进行测试。在使用mssql_e...

雷xiao雨
2014/03/01
0
0
折腾Oracle问题小菜记[分页存储过程/查询所有表、视图、存储过程/查询表、视图、存储过程字段结构与参数](三)

说明: 为了让CYQ.Data 框架支持Oracle,这几天对Oracle进行了基本探索,并把中间遇到的问题轻轻的记录了下来,与大伙共享。 总共有三篇: 1:初折腾Oracle问题小记 2:初折腾Oracle问题小记二...

晨曦之光
2012/03/09
0
0
jmeter(八)-JDBC请求(sqlserver)

做JDBC请求,首先要了解这个JDBC对象是什么,然后寻找响应的数据库连接URL和数据库驱动。 数据库URL:jdbc:sqlserver://200.99.197.190:1433;databaseName=ebank 数据库驱动:com.microsoft...

劲风online
2015/01/08
0
0
SQLSERVER 高效分页查询

Sqlserver数据库分页查询一直是Sqlserver的短板,闲来无事,想出几种方法,假设有表ARTICLE,字段ID、YEAR...(其他省略),数据53210条(客户真实数据,量不大),分页查询每页30条,查询第1500页...

木头
2013/01/30
0
0
基于AEAI ESB的处理大字段

1.背景 数据迁移中大字段一直是个比较头疼的问题,一旦大字段中值大于32K的话就无法用sql语句来实现插入和更新。通用的解决办法一般有两种:存储过程和JDBC。下面主要总结下基于AEAI ESB用J...

数通畅联
2015/01/22
0
0
linux环境php调用sqlServer2000存储过程遇到的问题

  在linux环境中用php查询sql server并不是很常见的需求.一般来说如果一定要用sql server,编程语言我会先c#;如果一定要用php,数据库我就选mysql.   这几天的开发中,需要从一个非开源的e...

吕明明
2013/03/14
0
0
小觑数据库(SqlServer)查询语句执行过程

近年来,越来越多的NoSql产品不断的以技术革命的者的身份跳出来:"你看哥是多么的快,你们关型型数据库真是战五渣阿"。是的,高性能的场景下NoSql真的很出彩。而我们关系型数据库只能在墙角哭...

Yunanw
2014/04/14
0
4
WAF Bypass数据库特性(MSsql探索篇)

0x01 背景 探索玩了Mysql特性,继续来探索一下MSsql特性。 0x02 测试 常见有5个位置即:select * from admin where id=1【位置一】union【位置二】select【位置三】1,2,db_name()【位置四】f...

bypass
2017/07/02
0
0
Mybatis 分页插件 PageHelper 5.1.4 发布

如果你也在用 Mybatis,建议尝试该分页插件,这一定是最方便使用的分页插件。 该插件目前支持以下数据库的物理分页 Oracle Mysql MariaDB SQLite Hsqldb PostgreSQL DB2 SqlServer(2005+) I...

Liuzh_533
04/23
0
0
SQL SERVER与mysql 的区别

2010-04-12 09:26:56| 分类: 寒窗磨剑|举报|字号 订阅 今天问了一下高手关于数据库的问题, 他的回答是,如果是做网站的话最好是用sqlserver,如果只是做企业内部的系统或者是单机的软件的话...

波利beryl
2014/11/10
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

JPA @MappedSuperclass 注解说明

基于代码复用和模型分离的思想,在项目开发中使用JPA的@MappedSuperclass注解将实体类的多个属性分别封装到不同的非实体类中。 1.@MappedSuperclass注解只能标准在类上:@Target({java.lang....

海博1600
11分钟前
0
0
Scala Configuration 相关API

Play使用了 Typesafe config library,但是也提供了一个有着更多Scala高级特性的的 Configuration 封装。不熟悉Typesafe配置的开发者可以移步 configuration文件的语法和特性文档。 读取配置...

Landas
今天
1
0
使用cookie技术 记住账号

1. 效果 2. 实现过程 2.1 前端 将用户的选中传递给后台 这个参数的获取是 参考:https://my.oschina.net/springMVCAndspring/blog/1860498 // var rememberLogin = $("#rememberLoginId").i...

Lucky_Me
今天
1
0
《趣谈网络协议》02之网络分层的真实含义

一、提出问题 1.提出问题 当你听到什么二层设备、三层设备、四层 LB 和七层 LB 中层的时候,是否有点一头雾水,不知道这些所谓的层,对应的各种协议具体要做什么“工作”? 2.这四个问题你弄...

aibinxiao
今天
2
0
Python3学习日志二 Python中的集合set和字典dict

1.集合set 定义一个集合set 我们可以看到定义集合set有两种不同的形式,如果要定义一个空的集合set不能用{}而是要用set();另外,集合是无序的,而且set中的元素是不可重复的,如果你定义了一...

Mr_bullshit
今天
0
0
adb 操作指令详解

ADB,即 Android Debug Bridge,它是 Android 开发/测试人员不可替代的强大工具,也是 Android 设备玩家的好玩具。 注:有部分命令的支持情况可能与 Android 系统版本及定制 ROM 的实现有关。...

孟飞阳
今天
0
0
nodejs安装以及环境配置(很好的node安装和配置文章,少走很多弯路)

一、安装环境 1、本机系统:Windows 10 Pro(64位) 2、Node.js:v6.9.2LTS(64位) 二、安装Node.js步骤 1、下载对应你系统的Node.js版本:https://nodejs.org/en/download/ 2、选安装目录进...

sprouting
今天
1
0
Redisson

了解了Redisson,发现使用挺简单的,接下来准备深入学习一下。 Redisson介绍 Redisson是架设于Redis基础之上的一个Java驻内存数据网格(In-Memory Data Grid) Redisson在基于NIO的Netty框架上...

to_ln
今天
0
0
python有哪些好玩的应用实现,用python爬虫做一个二维码生成器

python爬虫不止可以批量下载数据,还可以有很多有趣的应用,之前也发过很多,比如天气预报实时查询、cmd版的实时翻译、快速浏览论坛热门帖等等,这些都可以算是爬虫的另一个应用方向! 今天给...

python玩家
今天
0
0
python爬虫日志(3)-爬去异步加载网页

在浏览器检查元素页面中,选取Network中的XHR选项即可观察每次加载页面,网页发出的请求,观察url的规律即可利用封装的函数对每一页进行爬取。

茫羽行
今天
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部